/*****************************************************************************
*
* Filename         - $Workfile:   bag_trace_data.sql  $
*
* Author           - Dave Banks
*
* Project Name     - UltraTrak Core
*
* Source Code
* Identifier       - Database 
*
* Design Spec
* Section          -
*
* Contents         - Database script to create view BAG_TRACE_DATA
*
* Language         - Oracle SQL script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

PROMPT Creating View BAG_TRACE_DATA

CREATE OR REPLACE VIEW BAG_TRACE_DATA AS
SELECT bt.action_time AS action_date,
       TO_CHAR(bt.action_time,'HH24:MI') AS action_time,
       fa.agent_code, bt.airport_code,
       bt.bag_trace_time as bag_trace_date,
       TO_CHAR(bt.bag_trace_time,'HH24:MI') AS bag_trace_time,
       bt.bag_tag, bt.bag_tag1, bt.bag_tag2a, bt.bag_tag2b,
       bt.bag_tag3, bt.error_id, bt.error_description,
       bt.flight_date, bt.flight_id_scan, bt.flight_no,
       bt.location_code, bt.location_description,
       bt.loginname, bt.pax_id, bt.record_id, bt.scanner_id,
       (CASE
        WHEN bt.trace_action = 'STOP' and bt.stop_reason IS NOT NULL
        THEN TRIM(bt.trace_action) || '-' || bt.stop_reason
        ELSE bt.trace_action
        END) trace_action, bt.uld_no, bt.user_id, bt.alpha_code
FROM bag_trace bt JOIN flight_access fa ON bt.flight_id_scan = fa.flight_id
WHERE bt.flight_id_scan > 0
UNION ALL
SELECT bt.action_time AS action_date,
       TO_CHAR(bt.action_time,'HH24:MI') AS action_time,
       a.agent_code, bt.airport_code,
       bt.bag_trace_time AS bag_trace_date,
       to_char(bt.bag_trace_time,'HH24:MI') AS bag_trace_time,
       bt.bag_tag, bt.bag_tag1, bt.bag_tag2a, bt.bag_tag2b,
       bt.bag_tag3, bt.error_id, bt.error_description,
       bt.flight_date, bt.flight_id_scan, bt.flight_no,
       bt.location_code, bt.location_description,
       bt.loginname, bt.pax_id, bt.record_id, bt.scanner_id,
       (CASE 
        WHEN bt.trace_action = 'STOP' and bt.stop_reason IS NOT NULL 
        THEN TRIM(bt.trace_action) || '-' || bt.stop_reason
        ELSE bt.trace_action 
        END) trace_action, bt.uld_no, bt.user_id, bt.alpha_code
FROM agents a, bag_trace bt WHERE bt.flight_id_scan IS NULL OR bt.flight_id_scan = 0;
/

