/*****************************************************************************
*
* Filename         - $Workfile:   unknown_unauth_bag.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
*                    View over unknown_bag and unauthorised_bag to simplify
*                    display of this information in the MWS
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

PROMPT Creating view unknown_unauth_bag;

CREATE OR REPLACE FORCE VIEW unknown_unauth_bag (
  flight_id,
  flight_sch_time,
  flight_date,
  flight_no,
  flt_manually_entered,
  bag_tag,
  error_description,
  timestamp,
  location_code,
  loginname,
  bsm_received,
  pax_from_handler,
  pax_name,
  manual_pax_surname,
  manual_pax_initials,
  ok_to_fly,
  unk_unauth,
  bag_ident_code,
  id
) AS
(SELECT
    fli.flight_id,
    fli.flight_sch_time,
    fli.flight_date,
    fli.flight_no,
    unk.flt_manually_entered,
    unk.bag_tag,
    err.error_description,
    unk.timestamp,
    unk.location_code,
    ttu.loginname,
    bsm.bsm_log,
    DECODE(unk.pax_surname,NULL,'',
        TRIM(unk.pax_surname) || DECODE(unk.pax_full_inits,NULL,'','/') ||
            TRIM(unk.pax_full_inits)),
    DECODE(pax.pax_surname,NULL,'',
        TRIM(pax.pax_surname) || DECODE(pax.pax_full_inits,NULL,'','/') ||
            TRIM(pax.pax_full_inits)),
    unk.pax_surname,
    unk.pax_full_inits,
    DECODE(unk.ok_to_fly,NULL,'N',0,'N',1,'Y'),
    'K',
    unk.bag_ident_code,
    id
  FROM
      unknown_bag    unk,
      flight         fli,
      error          err,
      tagtrak_users  ttu,
      bsm_log        bsm,
      bsm_data       bsmd,
      pax            pax
  WHERE fli.flight_id(+) = unk.flight_id
  AND err.error_id(+) = unk.error_id
  AND ttu.user_id(+) = unk.user_id
  AND bsmd.pax_id(+) = unk.pax_id
  AND substr(bsmd.bag_tag(+),2) = substr(unk.bag_tag,2)
  AND bsm.bsm_id(+) = bsmd.bsm_id
  AND bsm.msg_type(+) = 'BSM'
  AND pax.pax_id(+) = unk.pax_id)
  UNION
  (SELECT
    fli.flight_id,
    fli.flight_sch_time,
    fli.flight_date,
    fli.flight_no,
    NULL,
    una.bag_tag,
    err.error_description,
    una.timestamp,
    una.location_code,
    ttu.loginname,
    bsm.bsm_log,
    NULL,
    DECODE(pax.pax_surname,NULL,'',
        TRIM(pax.pax_surname) || DECODE(pax.pax_full_inits,NULL,'','/') ||
            TRIM(pax.pax_full_inits)),
    null,
    null,
    DECODE(una.ok_to_fly,NULL,'N',0,'N',1,'Y'),
    'A',
    null,
    id
FROM
      unauthorised_bag  una,
      flight            fli,
      error             err,
      tagtrak_users     ttu,
      bsm_log           bsm,
      bsm_data          bsmd,
      pax               pax
  WHERE fli.flight_id(+) = una.flight_id
  AND err.error_id(+) = una.error_id
  AND ttu.user_id(+) = una.user_id
  AND bsmd.pax_id(+) = una.pax_id
  AND substr(bsmd.bag_tag(+),2) = substr(una.bag_tag,2)
  AND bsm.bsm_id(+) = bsmd.bsm_id
  AND bsm.msg_type(+) = 'BSM'
  AND pax.pax_id(+) = una.pax_id
  /* CA2639 - remove duplicate rows when multiple bag BSMs */
  AND bsmd.bsm_id = (select max(bd.bsm_id) from bsm_data bd
                     where bsmd.bag_tag = bd.bag_tag
                       and bsmd.pax_id = bd.pax_id));
