/*****************************************************************************
*
* Filename         - $Workfile : reflight_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 reflight_bag_v
   (flight_id,
    flight_date,
    flight_sch_date_time,
    flight_details,
    bag_tag,
    bag_tag1,
    bag_tag2a,
    bag_tag2b,
    bag_tag3,
    pax_name,
    pax_class,
    bag_dest,
    uld_no,
    uld_seq_no,
    reflighted_flight_details,
    reason_for_loss,
    fault_station,
    worldtracer_ref,
    loginname,
    reflight_date_time,
    reflighted, 
    for_reflighting,
    agent_code)
   AS
   SELECT rf.flight_id,
          rf.flight_date,
          to_date(rf.flight_date
                 ||rf.scheduled_time
                 ||TO_CHAR(rf.departure_time,'YYYY')
                 , 'DDMONHH24MIYYYY'),
          rf.flight_no||' '||rf.flight_date||' '||rf.scheduled_time||' ' ||rf.flight_dest,
          rb.bag_tag,
          rb.bag_tag1,
          rb.bag_tag2a,
          rb.bag_tag2b,
          ltrim(to_char(rb.bag_tag3,'000000')),
          rp.pax_initials||' '||rp.pax_surname,
          rp.pax_class,
          rb.bag_dest,
          rb.uld_no,
          rb.bag_sequence_no,
          rb.reflight_flight_no||' '||rb.reflight_flight_date||' '||rb.reflight_flight_time||' '||rb.reflight_flight_dest,
          rb.reason_for_loss,
          rb.fault_station,
          rb.worldtracer_ref,
          rb.reflight_login_name,
          rb.reflight_date_time,
          decode(rb.reflight_needed, 'A', 1, 0) reflighted,
          decode(rb.reflight_needed, 'Y', 1, 0) for_reflighting,
          fa.agent_code
   FROM reflight_flight   rf,
        reflight_pax      rp,
        reflight_bag      rb,
        flight_access     fa
   WHERE rf.flight_id     = rb.flight_id
   AND   rp.flight_id     = rb.flight_id
   AND   rp.pax_id        = rb.pax_id
   AND   fa.flight_id     = rf.flight_id
   UNION ALL
   SELECT rf.flight_id,
          rf.flight_date,
          to_date(rf.flight_date
                 ||rf.scheduled_time
                 ||TO_CHAR(rf.departure_time,'YYYY')
                 , 'DDMONHH24MIYYYY'),
          rf.flight_no||' '||rf.flight_date||' '||rf.scheduled_time||' ' ||rf.flight_dest,
          rb.bag_tag,
          rb.bag_tag1,
          rb.bag_tag2a,
          rb.bag_tag2b,
          ltrim(to_char(rb.bag_tag3,'000000')),
          rp.pax_initials||' '||rp.pax_surname,
          rp.pax_class,
          rb.bag_dest,
          rb.uld_no,
          rb.bag_sequence_no,
          rb.reflight_flight_no||' '||rb.reflight_flight_date||' '||rb.reflight_flight_time||' '||rb.reflight_flight_dest,
          rb.reason_for_loss,
          rb.fault_station,
          rb.worldtracer_ref,
          rb.reflight_login_name,
          rb.reflight_date_time,
          decode(rb.reflight_needed, 'A', 1, 0) reflighted,
          decode(rb.reflight_needed, 'Y', 1, 0) for_reflighting,
          fa.agent_code
   FROM live.reflight_flight   rf,
        live.reflight_pax      rp,
        live.reflight_bag      rb,
        arch.flight_access     fa
   WHERE rf.flight_id     = rb.flight_id
   AND   rp.flight_id     = rb.flight_id
   AND   rp.pax_id        = rb.pax_id
   AND   fa.flight_id     = rf.flight_id
/
