/*****************************************************************************
*
* Filename         - $Workfile:   inbound_uld_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_uld_v1 AS
SELECT   Nvl(iu.source_uld_no, iu.uld_number) uld_no
         ,uld_list.flight_id
         ,iu.inbound_uld_id
         ,iu.inbound_flight_id
         ,iu.source_uld_no
         ,iu.result_uld_no
         ,iu.result_uld_flight_id
         ,NVL(iu.uld_position, uld_list.uld_hold_no)  AS uld_hold_no
         ,NVL(uld_list.uld_no_of_bags, 0)             AS uld_no_of_bags
         ,NVL(uld_list.next_bag_seq_no, 0)            AS next_bag_seq_no
         ,NVL(uld_list.uld_loaded_status, 'Y')        AS uld_loaded_status
         ,NVL(uld_list.uld_category, Nvl(iu.uld_category, '*')) AS uld_category
         ,NVL(uld_list.uld_type, Nvl(iu.uld_type, 'X'))         AS uld_type
         ,uld_list.connection_type
         ,uld_list.uld_load_time
         ,uld_list.user_id
         ,uld_list.location_code
         ,Nvl(uld_list.pd_marker, 'Y') AS pd_marker
         ,uld_list.uld_id_code
         ,uld_list.next_flight_no
         ,uld_list.next_flight_date
         ,Nvl(uld_list.close_flag, 'Y') AS close_flag
         ,iu.source_message_id
         ,iu.result_uld_status
         ,iu.error_code
         ,iu.inbound_uld_status
    FROM inbound_uld iu,
         (SELECT fo.next_leg_id, u.* FROM flight_outbound_v1 fo, ulds u WHERE fo.flight_id = u.flight_id) uld_list
    WHERE iu.source_uld_no = uld_list.uld_no(+)
      AND iu.inbound_flight_id = uld_list.next_leg_id(+);
/

