
rem *********************************************************************

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Filename:             arrival_flight_loading_v.sql                *

rem * Module Name:          arrrival_flight_loading_v                   *

rem * Subsystem Name:       views                                       *

rem * Originating Author:   James Creegan                               *

rem * Design Reference:     FAS/BBTRK/SD/225                            *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Description:                                                      *

rem *  View required for Inbound flight screen                          *

rem *********************************************************************

rem *                                                                   *

rem * Program Identification Array:                                     *

rem *                                                                   *

rem * "%Z%%M% VERSION %I% %E% %U%"

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Modification History:                                             *

rem *                                                                   *

rem * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *

rem * --- -------------- --------  ------------------------------------ *

rem * 1.0 28 May 03                File initially checked in            *

rem *********************************************************************/
PROMPT Creating view arrival_flight_loading_v

CREATE OR REPLACE FORCE VIEW arrival_flight_loading_v
(uld_no,
 uld_posn,
 uld_cat,
 uld_type,
 uld_class,
 uld_count,
 uld_excep_count,
 uld_thru,
 uld_trans_no,
 uld_trans_date,
 uld_trans_id,
 in_flight_id,
 out_site_id)
AS
SELECT iuld.uld_no,
       iuld.uld_hold_no,
       iuld.uld_category,
       iuld.uld_type,
       NVL2(iuld.source_message_id, '*', uld_class_list (iuld.uld_no, orig_flt.flight_id)),
       count_by_uld(iuld.inbound_flight_id, iuld.uld_no, 0),
       count_by_uld(iuld.inbound_flight_id, iuld.uld_no, 1),
       DECODE(iuld.inbound_uld_status,'THROUGH','Y','N'),
       flt_out.flight_no,         --ULD_TRANS_* columns  will only be set
       flt_out.flight_date,       --when the ULD has been transferred
       flt_out.flight_id,         --to the Outbound Flight.
       iuld.inbound_flight_id,
       flt_out.site_id
FROM   inbound_uld_v1 iuld,
       flight_outbound_v1 flt_out,
       flight_outbound_v1 orig_flt
WHERE  iuld.result_uld_flight_id   = flt_out.flight_id (+)
  AND  iuld.result_uld_status IN ('CREATED','AMENDED','UNPROCESSED')
  AND  iuld.inbound_flight_id = orig_flt.next_leg_id(+);
