/*****************************************************************************
*
* Filename         - $Workfile:   inbound_bag_v1.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
*
*****************************************************************************/
CREATE OR REPLACE FORCE VIEW inbound_bag_v1 AS
SELECT ib.source_bag_id                   AS bag_id 
         ,ib.inbound_bag_id 
         ,ib.result_bag_id 
         ,NVL(b.pax_id, 0)                  AS pax_id 
         ,ib.inbound_pax_id 
         ,Nvl(b.uld_no, iu.uld_number) AS uld_no 
         ,ib.inbound_uld_id 
         ,NVL(b.authority_to_load_dcs, 'N') AS authority_to_load_dcs 
         ,NVL(b.authority_to_load_brs, 'N') AS authority_to_load_brs 
         ,NVL(b.unassociated_bag, 'N')      AS unassociated_bag 
         ,NVL(b.bag_destination, ip.pax_destination) AS bag_destination 
         ,NVL(ib.bag_exception_type, b.bag_exception)   AS bag_exception 
         ,NVL(b.bag_source_indicator, 'T')  AS bag_source_indicator 
         ,NVL(b.bag_source_location, m.originating_airport) AS bag_source_location 
         ,NVL(b.loaded_status, 'Y')         AS loaded_status 
         ,NVL(ib.bag_load_sequence, b.bag_sequence_no)  AS bag_sequence_no 
         ,b.time_loaded 
         ,NVL(b.time_bag_stored, TRUNC(SYSDATE))        AS time_bag_stored 
         ,NVL(b.delete_marker, 0)           AS delete_marker 
         ,CAST (NVL(ib.bag_tag, b.bag_tag) AS CHAR(10))       AS bag_tag 
         ,CAST (Nvl(b.bag_tag1, SubStr(ib.bag_tag, 1, 1)) AS CHAR(1)) AS bag_tag1 
         ,CAST (Nvl(b.bag_tag2a, SubStr(get_user_bag_tag(ib.bag_tag, 'A', 0), 1, 2)) AS CHAR(3)) AS bag_tag2a 
         ,CAST (NVL(b.bag_tag2b, SUBSTR(ib.bag_tag,-9,3)) AS CHAR(3))      AS bag_tag2b 
         ,NVL(b.bag_tag3, TO_NUMBER(SUBSTR(ib.bag_tag,-6))) AS bag_tag3 
         ,CAST (NVL(b.location_code, NVL(multi_site.get_auto_location(m.originating_airport), 'UNKNWN')) AS CHAR(6))     AS location_code 
		 ,b.scanner_id 
         ,b.process_name 
         ,b.unit_name 
         ,b.dcsid 
         ,b.subunit_name 
         ,b.uld_flight_id 
         ,b.dcs_bag_tag 
         ,NVL(ib.bag_security_status, NVL(b.security_clearance_status, 'N')) 
            AS security_clearance_status 
         ,b.sc_user_id 
         ,b.sc_location 
         ,b.sc_time 
         ,b.connection_type 
         ,b.bag_type 
         ,b.next_flight_no 
         ,b.next_flight_date 
         ,Nvl(b.hbmd_flag, 'Y') AS hbmd_flag 
         ,b.group_no 
         ,b.delivery_instr 
         ,b.worldtracer_ref 
         ,NVL(ib.bag_weight, NVL(b.kg_weight, 0))      AS kg_weight 
         ,b.bvis_subheader 
         ,ib.source_message_id 
         ,ib.error_code 
         ,NVL(ib.result_bag_status, 'UNPROCESSED')     AS result_bag_status 
    FROM inbound_bag ib, 
         bag b, 
         messages m, 
         inbound_pax ip, 
         flight f, 
         inbound_uld iu 
   WHERE ib.source_bag_id = b.bag_id(+) 
     AND ib.source_message_id = m.message_id(+) 
     AND ib.inbound_pax_id = ip.inbound_pax_id 
     AND ip.inbound_flight_id = f.flight_id 
     AND iu.inbound_uld_id(+) = ib.inbound_uld_id;


