/*****************************************************************************
*
* Filename         - $Workfile:   bag_v5.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
*
*****************************************************************************/

PROMPT Creating view   bag_v5 
CREATE OR REPLACE FORCE VIEW   bag_v5   
(bag_tag  , pax_id  , uld_no  , authority_to_load_dcs  , 
 authority_to_load_brs  , unassociated_bag  , bag_destination  , bag_exception  ,
 bag_source_indicator  , loaded_status  , bag_sequence_no  , time_loaded  ,
 time_bag_stored  , delete_marker  , bag_tag1  , bag_tag2a  , bag_tag2b  , 
 bag_tag3  , location_code  , scanner_id  , uld_flight_id  ,
 loginname , 
 pax_status, pax_class,
 location_description,
 flight_id, booked_flight_id, flight_no, flight_date,
 inbound_flight_no, security_clearance_status, kg_weight, vfdb, authority_source
) AS
 SELECT 
 bag.bag_tag,  bag.pax_id,  bag.uld_no,  bag.authority_to_load_dcs, 
 bag.authority_to_load_brs, bag.unassociated_bag,  
 bag.bag_destination,  bag.bag_exception,
 bag.bag_source_indicator,  bag.loaded_status,  bag.bag_sequence_no, 
 TO_CHAR(bag.time_loaded, 'HH24:MI'), 
 TO_CHAR(bag.time_bag_stored, 'HH24:MI'), 
 bag.delete_marker,  bag.bag_tag1,  bag.bag_tag2a,  bag.bag_tag2b, 
 bag.bag_tag3,  bag.location_code,  bag.scanner_id,  bag.uld_flight_id , 
 '  ', 
 pax.pax_status,  DECODE(NVL(RTRIM(pax.pax_bags_class),pax.pax_class),pax.pax_class,NVL(RTRIM(pax.pax_bags_class),pax.pax_class), pax.pax_class || '/' || pax.pax_bags_class),
 loc.LOCATION_DESCRIPTION,
 flight.flight_id, pax.booked_flight_id, flight.flight_no, flight.flight_date,
 pax.inbound_flight_no, bag.security_clearance_status,
 bag.kg_weight, pax.vfdb, bag.authority_source
FROM  bag, pax, location loc, flight
WHERE  bag.location_code = loc.location_code 
AND  pax.pax_id = bag.pax_id
AND  pax.flight_id = flight.flight_id;

