/*********************************************************************************
*         - $Workfile:   rfid_trace_v1.sql  $
*
*                     Name: Yomi Kogbe    Date: 03/05/00  Version1.0     CR Number:14954
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema View
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
PROMPT Creating view rfid_trace_v1;
CREATE OR REPLACE FORCE VIEW rfid_trace_v1
(   flight_id,
    flight_no,
    bag_trace_date,
    trace_action,
    bag_tag,
    location_description,
    loginname,
    agent_code,
    error_description,
    uld_no
) as
select  btd.flight_id_scan, 
        btd.flight_no,
        btd.bag_trace_time,
        btd.trace_action,
        btd.bag_tag,
        btd.location_description,
        btd.loginname,
        btd.agent_code,
        btd.error_description,
        bt.uld_no
 from   bag_trace_data btd,
        bag_trace      bt
where   btd.bag_tag                                  = bt.bag_tag
and     btd.pax_id                                   = bt.pax_id
and     btd.trace_action                             = bt.trace_action
----------------------------------------------------------------------------------------------------------
-- the date/times should be within a microsecond - should not compare floating point numbers for equality!
----------------------------------------------------------------------------------------------------------
and     ABS (btd.bag_trace_date - bt.bag_trace_time) < 1 / (24 * 60 * 60 * 1000000)
and     btd.agent_code                               = 'RFID';
