/*****************************************************************************
*
* Filename         - $Workfile:   $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema: EQ_LOG table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   $  $Revision:   $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   $
*
****************************************************************************/

PROMPT Creating view EQ_LOG_V ;

CREATE OR REPLACE FORCE VIEW EQ_LOG_V  AS
--
-- This select gets all of the equipment logs that have been entered into table EQ_LOG.
--
select
        site_info.airport_code, eql_kit_type kit_type, eql_serial_number serial_number,
        firstname, surname, kis_status kit_status, eql_comment log_comment, eql_timestamp timestamp,
        eql_id, kis_id, user_id, 0 kit_id, 0 user_trace_id
from
        site_info, eq_log, kit_status, tagtrak_users
where
        eql_site_id = site_info.site_id (+)
  and   eql_kis_id = kis_id (+)
  and   eql_user_id = tagtrak_users.user_id (+)
UNION ALL
--
-- This select creates one equipment log per each equipment that exists in KIT for the latest
-- Login and Log out so that the MWS Equipment Maintenance screen can be used to establish the
-- current status of a piece of kit.
--
-- Note that I saw more than user_trace row for the same kit with the same insert_date (see
-- nested select latest_traces). So, I used the max(id) to get only one (nested select
-- very_latest_trace). This gives us a problem when the sequence value wraps around but
-- I think that this is acceptable to keep it simple and seeing as we're only getting
-- the timestamp and comment columns from user_trace anyway.
--
select
        k.airport_code, type kit_type, serial_number, firstname, surname,
        decode(trace_action, 'LOG-IN','LOGGED IN','LOG-OUT','AVAILABLE', '<archived>') kit_status,
        trace_action log_comment, ut.insert_date timestamp,
        0 eql_id, 0 kis_id, k.user_id, k.id kit_id, ut.id user_trace_id
from
        kit k, tagtrak_users,
        (
                select  user_trace.id, kit_id, insert_date, trace_action
                from    user_trace,
                (
                        select    max(id) id
                        from      user_trace,
                        (
                                select kit_id, max(insert_date) insert_date
                                from   user_trace
				where  error_id >= 0
				group by kit_id
                        ) latest_traces
                where   latest_traces.kit_id = user_trace.kit_id
                and     latest_traces.insert_date = user_trace.insert_date
		and     error_id >= 0
                group by        latest_traces.kit_id
                ) very_last_trace
                where   user_trace.id = very_last_trace.id
        ) ut
where
      k.user_id = tagtrak_users.user_id (+)
and   k.id = ut.kit_id (+)
/

