/*****************************************************************************
*
* Filename         - $Workfile:   bmms_sent_v1.sql  $
*
*                     Name              Date      Version     CR Number
* 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
*
*****************************************************************************/

CREATE OR REPLACE FORCE VIEW bmms_sent_v1
(
carrier,
flight_no,
flight_date,
departure_time,
number_of_messages,
number_of_parts
) AS
SELECT m.carrier,
       m.flight_number,
       m.flight_date,
       to_char(inbound_flight_utils.flt_date_to_date(m.flight_date), 'YYYYMMDD'),
       Count(DISTINCT(m.message_id)),
       Count(mt.message_part_id)
FROM messages_trace mt,
     messages m,
     message_parts mp
WHERE mt.message_part_id = mp.message_part_id
  AND mp.message_id = m.message_id
  AND mt.trace_action = 'MESSAGE_SEND'
  AND m.message_direction = 'O'
GROUP BY m.carrier,
         m.flight_number,
         m.flight_date,
         inbound_flight_utils.flt_date_to_date(m.flight_date);

