/*****************************************************************************
*
* Filename         - $Workfile : reflighted_bag_v.sql $
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - FAS/FARFL
*
* Description      - Re-flighting Module
*
* Language         - SQL Script
*
* File History     - For the development history run the Show History report in PVCS
*
* Coptright Ultra Electronics Airport Systems 2004
*
*****************************************************************************/
CREATE OR REPLACE FORCE VIEW reflighted_bag_v
(flight_id,
 flight_sch_date_time,
 flight_details,
 prev_flight_details,
 bag_tag,
 bag_tag1,
 bag_tag2a,
 bag_tag2b,
 bag_tag3,
 prev_pax_name,
 bag_dest,
 uld_no,
 uld_seq_no,
 hold_position,
 loaded,
 reason_for_loss,
 fault_station,
 worldtracer_ref,
 loginname,
 reflight_date_time,
 agent_code)
AS
SELECT rb.reflight_flight_id,
       to_date(rf.flight_date
              ||rf.scheduled_time
              ||TO_CHAR(rf.departure_time,'YYYY')
              , 'DDMONHH24MIYYYY') "SCHDATETIME",
       rb.reflight_flight_no||' '||rb.reflight_flight_date||' '||rb.reflight_flight_time||' '||rb.reflight_flight_dest "REF-FLTDETS",
       rf.flight_no||' '||rf.flight_date||' '||rf.scheduled_time||' '||rf.flight_dest "FLTDETS",
       rb.bag_tag,
       rb.bag_tag1,
       rb.bag_tag2a,
       rb.bag_tag2b,
       ltrim(to_char(rb.bag_tag3,'000000')),
       trim(rb.orig_pax_init)||' '||trim(rb.orig_pax_surname) "PAX INFO",
       rb.bag_dest,
       b.uld_no,
       b.bag_sequence_no,
       u.uld_hold_no,
       decode(b.loaded_status,
              'Y', decode(b.uld_no,
                          NULL, 'Y',
                          decode(u.uld_load_time,
                                 NULL, 'N',
                                 'Y')),
              'N') "LOADED",
       rb.reason_for_loss,
       rb.fault_station,
       rb.worldtracer_ref,
       rb.reflight_login_name,
       rb.reflight_date_time,
       fa.agent_code
FROM reflight_flight   rf,
     reflight_pax      rp,
     reflight_bag      rb,
     bag               b,
     pax               p,
     ulds              u,
     flight_access     fa
WHERE rf.flight_id          = rb.flight_id
AND   b.pax_id(+)           = rb.reflight_pax_id
AND   b.bag_tag(+)          = rb.reflight_bag_tag
AND   p.pax_id(+)           = rb.reflight_pax_id
AND   rp.pax_id             = rb.pax_id
AND   u.flight_id(+)        = b.uld_flight_id
AND   u.uld_no(+)           = b.uld_no
AND   rb.reflight_flight_id IS NOT NULL
AND   fa.flight_id          = rb.flight_id
UNION ALL
SELECT rb.reflight_flight_id,
       to_date(rf.flight_date
              ||rf.scheduled_time
              ||TO_CHAR(rf.departure_time,'YYYY')
              , 'DDMONHH24MIYYYY') "SCHDATETIME",
       rb.reflight_flight_no||' '||rb.reflight_flight_date||' '||rb.reflight_flight_time||' '||rb.reflight_flight_dest "REF-FLTDETS",
       rf.flight_no||' '||rf.flight_date||' '||rf.scheduled_time||' '||rf.flight_dest "FLTDETS",
       rb.bag_tag,
       rb.bag_tag1,
       rb.bag_tag2a,
       rb.bag_tag2b,
       ltrim(to_char(rb.bag_tag3,'000000')),
       trim(rb.orig_pax_init)||' '||trim(rb.orig_pax_surname) "PAX INFO",
       rb.bag_dest,
       b.uld_no,
       b.bag_sequence_no,
       u.uld_hold_no,
       decode(b.loaded_status,
              'Y', decode(b.uld_no,
                          NULL, 'Y',
                          decode(u.uld_load_time,
                                 NULL, 'N',
                                 'Y')),
              'N') "LOADED",
       rb.reason_for_loss,
       rb.fault_station,
       rb.worldtracer_ref,
       rb.reflight_login_name,
       rb.reflight_date_time,
       fa.agent_code
FROM live.reflight_flight   rf,
     live.reflight_pax      rp,
     live.reflight_bag      rb,
     arch.bag               b,
     arch.pax               p,
     arch.ulds              u,
     arch.flight_access     fa
WHERE rf.flight_id          = rb.flight_id
AND   b.pax_id(+)           = rb.reflight_pax_id
AND   b.bag_tag(+)          = rb.reflight_bag_tag
AND   p.pax_id(+)           = rb.reflight_pax_id
AND   rp.pax_id             = rb.pax_id
AND   u.flight_id(+)        = b.uld_flight_id
AND   u.uld_no(+)           = b.uld_no
AND   rb.reflight_flight_id IS NOT NULL
AND   fa.flight_id          = rb.flight_id
/
