/*****************************************************************************
*
* Filename         - $Workfile:   bmm_bags.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, 2000
*
*****************************************************************************/
PROMPT Creating view bag_v8

CREATE OR REPLACE FORCE VIEW bag_v8
(
    inbound_flight_id
  , inbound_flight_no
  , inbound_flight_date
  , arrival_time
  , outbound_flight_id
  , outbound_flight_no
  , outbound_flight_date
  , departure_time
  , flight_destination
  , bag_tag
  , pax_surname
  , pax_initials
  , pax_sequence_no
  , uld_no
  , bag_sequence_no
  , uld_hold_no
  , transfer_time
  , max_mct
  , min_mct
  , agent_code
  , outbound
  , inbound
)
AS
SELECT fin.flight_id,
       fin.flight_no,
       fin.flight_date,
       nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fin.departure_time, inbound_flight_utils.flt_date_to_date(fin.flight_date))))),
       fout.flight_id,
       fout.flight_no,
       fout.flight_date,
       nvl (fi.act_flight_time, nvl (fi.est_flight_time, nvl (fi.schd_flight_time, inbound_flight_utils.flt_date_to_date(fout.flight_date) +  To_number(SubStr(fout.flight_sch_time, 1, 2))/24 + To_Number(SubStr(fout.flight_sch_time, 3))/1440))),
       fod.flight_destination,
       ib.bag_tag,
       ip.pax_surname,
       ip.pax_initials,
       ip.pax_sequence_no,
       decode(h.hold_no, NULL, iu.uld_no, NULL),
       ib.bag_sequence_no,
       h.hold_no,
       (nvl (fi.act_flight_time, nvl (fi.est_flight_time, nvl (fi.schd_flight_time, inbound_flight_utils.flt_date_to_date(fout.flight_date) +  To_number(SubStr(fout.flight_sch_time, 1, 2))/24 + To_Number(SubStr(fout.flight_sch_time, 3))/1440))) -
           nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fin.departure_time, inbound_flight_utils.flt_date_to_date(fin.flight_date)))))) * 24 * 60,
       get_max_con_time (trim (fii.gate), trim (fi.gate), fout.site_id, fl.agent_code),
       get_min_con_time (trim (fii.gate), trim (fi.gate), fout.site_id, fl.agent_code),
       fl.agent_code,
       decode (fi.flight_no, null, 0, 1),
       decode (fii.flight_no, null, 0, 1)
FROM   flight_inbound_v1 fin,
       flight_info_inbound fii,
       flight_outbound_v1 fout,
       flight_info fi,
       flight_dests fod,
       inbound_bag_v1 ib,
       inbound_pax_v1 ip,
       inbound_uld_v1 iu,
       holds h,
       flights_linked_v2 fl,
       inbound_pax_onward_dests ipod
WHERE  fii.flight_no(+)     = fin.flight_no
  AND  fii.flight_date(+)   = fin.flight_date
  AND  fii.airport_code(+)  = multi_site.get_airport_code(fin.site_id)
  AND  fout.flight_id       = fl.out_flight_id
  AND  fin.flight_id        = fl.in_flight_id
  AND  fi.flight_no(+)      = fout.flight_no
  AND  fi.flight_date(+)    = fout.flight_date
  AND  fi.airport_code(+)   = multi_site.get_airport_code(fout.site_id)
  AND  fod.flight_id        = fout.flight_id
  AND  fod.dest_order       = 1
  AND  ip.inbound_flight_id = fin.flight_id
  AND  ib.inbound_pax_id    = ip.inbound_pax_id
  AND  iu.inbound_uld_id    = ib.inbound_uld_id
  AND  iu.inbound_flight_id = fin.flight_id
  AND  iu.uld_no            = h.hold_no(+)
  AND  iu.result_uld_status IN ('CREATED','AMENDED','UNPROCESSED')
  AND  ib.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED')
  AND  ip.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED')
  AND  ipod.inbound_pax_id  = ip.inbound_pax_id
  AND  fout.flight_no = ipod.onward_flight_no
  AND  fout.flight_date = ipod.onward_flight_date
  AND  ipod.destination_sequence = 1
;

------------------------------------
PROMPT Creating view bag_v9

CREATE OR REPLACE FORCE VIEW bag_v9 (
  inbound_flight_id,
  inbound_flight_no,
  inbound_flight_date,
  outbound_flight_id,
  outbound_flight_no,
  outbound_flight_date,
  sdt,
  departure_time,
  agent_code,
  bags
) AS
SELECT fi.flight_id,
       p.inbound_flight_no,
       p.inbound_date,
       fo.flight_id,
       fo.flight_no,
       fo.flight_date,
       TO_CHAR(nvl (finf.act_flight_time, nvl (finf.est_flight_time, nvl (finf.schd_flight_time, inbound_flight_utils.flt_date_to_date(fo.flight_date) +  To_number(SubStr(fo.flight_sch_time, 1, 2))/24 + To_Number(SubStr(fo.flight_sch_time, 3))/1440))),'HH24:MI'),
       nvl (finf.act_flight_time, nvl (finf.est_flight_time, nvl (finf.schd_flight_time, inbound_flight_utils.flt_date_to_date(fo.flight_date) +  To_number(SubStr(fo.flight_sch_time, 1, 2))/24 + To_Number(SubStr(fo.flight_sch_time, 3))/1440))),
       fao.agent_code,
       Count(b.bag_id)
FROM   flight_outbound_v1 fo,
       pax p,
       bag b,
       flight_access fai,
       flight_access fao,
       flight_inbound_v1 fi,
       flight_info finf
WHERE  fo.flight_id = p.flight_id
  AND  p.pax_id = b.pax_id
  AND  fo.flight_id = fao.flight_id
  AND  p.inbound_flight_no IS NOT NULL
  AND  p.inbound_date IS NOT NULL
  AND  fi.flight_id = fai.flight_id
  AND  fai.agent_code = fao.agent_code
  AND  fi.flight_no = p.inbound_flight_no
  AND  fi.flight_date = p.inbound_date
  AND  fi.site_id = fo.site_id
  AND  finf.flight_no(+)      = fo.flight_no
  AND  finf.flight_date(+)    = fo.flight_date
  AND  finf.airport_code(+)   = multi_site.get_airport_code(fo.site_id)
GROUP BY fi.flight_id,
         p.inbound_flight_no,
         p.inbound_date,
         fo.flight_id,
         fo.flight_no,
         fo.flight_date,
         TO_CHAR(nvl (finf.act_flight_time, nvl (finf.est_flight_time, nvl (finf.schd_flight_time, inbound_flight_utils.flt_date_to_date(fo.flight_date) +  To_number(SubStr(fo.flight_sch_time, 1, 2))/24 + To_Number(SubStr(fo.flight_sch_time, 3))/1440))),'HH24:MI'),
         nvl (finf.act_flight_time, nvl (finf.est_flight_time, nvl (finf.schd_flight_time, inbound_flight_utils.flt_date_to_date(fo.flight_date) +  To_number(SubStr(fo.flight_sch_time, 1, 2))/24 + To_Number(SubStr(fo.flight_sch_time, 3))/1440))),
         fao.agent_code
;

-------------------------------------
PROMPT Creating view bag_v10

CREATE OR REPLACE FORCE VIEW bag_v10
(
  outbound_flight_id,
  outbound_flight_no,
  outbound_flight_date,
  inbound_flight_id,
  inbound_flight_no,
  inbound_flight_date,
  eta,
  bags,
  hot_connection,
  transfer_time,
  max_mct,
  min_mct,
  agent_code,
  outbound,
  inbound
) AS
SELECT bv9.outbound_flight_id,
       bv9.outbound_flight_no,
       bv9.outbound_flight_date,
       bv9.inbound_flight_id,
       bv9.inbound_flight_no,
       bv9.inbound_flight_date,
       to_char(nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fi.departure_time, inbound_flight_utils.flt_date_to_date(bv9.inbound_flight_date))))),'HH24:MI'),
       count (b.bag_id),
       hot_connection(nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fi.departure_time, inbound_flight_utils.flt_date_to_date(bv9.inbound_flight_date))))), bv9.departure_time),
       (bv9.departure_time -  nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fi.departure_time, inbound_flight_utils.flt_date_to_date(bv9.inbound_flight_date)))))) * 24 * 60,
       get_max_con_time (trim (fii.gate), trim (finf.gate), fo.site_id, bv9.agent_code),
       get_min_con_time (trim (fii.gate), trim (finf.gate), fo.site_id, bv9.agent_code),
       bv9.agent_code,
       decode (finf.flight_no, null, 0, 1),
       decode (fii.flight_no, null, 0, 1)
FROM   bag_v9 bv9,
       flight_outbound_v1 fo,
       flight_info_inbound fii,
       flight_info finf,
       flight_inbound_v1 fi,
       pax p,
       bag b
WHERE  bv9.outbound_flight_id = fo.flight_id
  AND  fii.flight_no(+)     = bv9.inbound_flight_no
  AND  to_char(fii.trip_date(+), 'DDMON') = bv9.inbound_flight_date
  AND  Nvl(fii.airport_code, multi_site.get_airport_code(fo.site_id))  = multi_site.get_airport_code(fo.site_id)
  AND  finf.flight_no(+)      = fo.flight_no
  AND  finf.flight_date(+)    = fo.flight_date
  AND  finf.airport_code(+)   = multi_site.get_airport_code(fo.site_id)
  AND  fi.flight_id(+) = bv9.inbound_flight_id
  AND  p.flight_id = bv9.outbound_flight_id
  AND  p.inbound_flight_no = bv9.inbound_flight_no
  AND  p.inbound_date = bv9.inbound_flight_date
  AND  b.pax_id = p.pax_id
  AND  b.delete_marker = 0
GROUP BY  bv9.outbound_flight_id,
       bv9.outbound_flight_no,
       bv9.outbound_flight_date,
       bv9.inbound_flight_id,
       bv9.inbound_flight_no,
       bv9.inbound_flight_date,
       to_char(nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fi.departure_time, inbound_flight_utils.flt_date_to_date(bv9.inbound_flight_date))))),'HH24:MI'),
       hot_connection(nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fi.departure_time, inbound_flight_utils.flt_date_to_date(bv9.inbound_flight_date))))), bv9.departure_time),
       (bv9.departure_time -  nvl (fii.act_arrival_time, nvl (fii.est_arrival_time, nvl (fii.schd_arrival_time, nvl (fi.departure_time, inbound_flight_utils.flt_date_to_date(bv9.inbound_flight_date)))))) * 24 * 60,
       get_max_con_time (trim (fii.gate), trim (finf.gate), fo.site_id, bv9.agent_code),
       get_min_con_time (trim (fii.gate), trim (finf.gate), fo.site_id, bv9.agent_code),
       bv9.agent_code,
       decode (finf.flight_no, null, 0, 1),
       decode (fii.flight_no, null, 0, 1)
;

-------------------------------------
PROMPT Creating view bag_v11

CREATE OR REPLACE FORCE VIEW bag_v11
(
    outbound_flight_id
  , outbound_flight_no
  , outbound_flight_date
  , onward_flight_no
  , onward_flight_date
  , onward_flight_destination
  , bags
) AS
SELECT
    f.flight_id
  , flight_no
  , f.flight_date
  , NVL(onf.norm_fno,'TERM')
  , NVL(onf.odate,'TERM')
  , onf.dest
  , COUNT(*)
FROM
    flight_outbound_v1  f
  , onward_flight onf
  , pax p
  , bag b
WHERE f.flight_id = p.flight_id
AND   p.pax_id = b.pax_id
AND   onf.pax_id(+) = p.pax_id
AND   onf.dest_order(+) = 1
AND   b.delete_marker = 0
GROUP BY
    f.flight_id
  , f.flight_no
  , f.flight_date
  , onf.norm_fno
  , onf.odate
  , onf.dest
;

-------------------------------------
PROMPT Creating view bag_v12

CREATE OR REPLACE FORCE VIEW bag_v12
(
    inbound_flight_id
   ,inbound_flight_no
   ,inbound_flight_date
   ,eta
   ,outbound_flight_id
   ,outbound_flight_no
   ,outbound_flight_date
   ,sdt
   ,flight_destination
   ,bag_tag
   ,pax_surname
   ,pax_initials
   ,pax_sequence_no
   ,uld_no
   ,bag_sequence_no
   ,uld_hold_no
   ,agent_code
)AS
SELECT bv10.inbound_flight_id,
       bv10.inbound_flight_no,
       bv10.inbound_flight_date,
       bv10.eta,
       bv10.outbound_flight_id,
       bv10.outbound_flight_no,
       bv10.outbound_flight_date,
       TO_CHAR(fo.departure_time,'HH24:MI'),
       fd.flight_destination,
       b.bag_tag,
       p.pax_surname,
       p.pax_initials,
       p.pax_sequence_no,
       b.uld_no,
       b.bag_sequence_no,
       u.uld_hold_no,
       bv10.agent_code
FROM   bag_v10 bv10,
       flight fi,
       flight fo,
       flight_dests fd,
       pax p,
       bag b,
       ulds u
WHERE  fi.flight_id = bv10.inbound_flight_id
  AND  fo.flight_id = bv10.outbound_flight_id
  AND  fd.flight_id = fo.flight_id
  AND  fd.dest_order = 1
  AND  p.flight_id = bv10.outbound_flight_id
  AND  p.inbound_flight_no = bv10.inbound_flight_no
  AND  p.inbound_date = bv10.inbound_flight_date
  AND  p.pax_id = b.pax_id
  AND  p.delete_marker = 0
  AND  b.delete_marker = 0
  AND  b.uld_no = u.uld_no(+)
  AND  b.uld_flight_id = u.flight_id(+);



