/*****************************************************************************
*
* Filename         - $Workfile:   manual_bmm_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 manual_bmm_v1 (
  flight_no,
  flight_date,
  message_id,
  destination,
  route_id,
  agent_code,
  route_description
) AS
SELECT m.flight_number,
       m.flight_date,
       m.message_id,
       br.destination,
       br.route_id,
       fa.agent_code,
       NVL(br.route_description, br.destination)
FROM   messages m,
       bmm_routes br,
       carrier_site_config csc,
       flight_access fa,
       flight_dests fd,
       flight f
WHERE  TRIM(br.carrier) = TRIM(m.carrier)
  AND  br.site_id = f.site_id
  AND  csc.site_id = br.site_id
  AND  TRIM(csc.alpha_code) = TRIM(br.carrier)
  AND  csc.config_item = 'SEND_BMM'
  AND  csc.config_value = 'MANUAL'
  AND  csc.site_id = f.site_id
  AND  f.flight_id = m.flight_id
  AND  m.flight_id = fa.flight_id
  AND  fd.flight_id = m.flight_id
  AND  fd.flight_destination = br.destination
  AND  fd.dest_order = 1
  AND  m.processed_state <> 'R'
  AND  m.message_direction = 'O'
  AND  NOT EXISTS ( SELECT 1
                    FROM   messages_trace mt
                    WHERE  mt.message_id = m.message_id
                      AND  mt.message_route_id = br.route_id
                      AND  mt.trace_action = 'MESSAGE_SEND' )
  AND  NOT EXISTS ( SELECT 1
                    FROM   mail_indiv mi,
                           message_parts mp
                    WHERE  mi.msg_type = 'BMM'
                      AND  mp.message_id = m.message_id
                      AND  mi.msg_text = TRIM(TO_CHAR(mp.message_part_id)) || ',' || TRIM(br.subunit_name) || ',' || TRIM(TO_CHAR(br.route_id)));

