/*****************************************************************************
*
* Filename         - $Workfile:   uld_loading_progress_v.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, 2000
*
*****************************************************************************/
PROMPT Creating view uld_loading_progress_v 

CREATE OR REPLACE FORCE
VIEW uld_loading_progress_v
(flight_id
,flight_no
,flight_date
,uld_destination
,uld_class_list
,uld_category
,uld_type
,connection_type
,uld_no
,uld_no_of_bags
,rush_bags -- CA00459
,uld_hold_no
,uld_load_time
,first_bag_loaded
,last_bag_loaded
,last_bag_traced
,uld_weight
,max_uld_weight
,close_flag
,username
,trace_time
,volume_code)
AS
SELECT u.flight_id
,f.flight_no
,f.flight_date
,d.uld_destination
,d.uld_class_list
,u.uld_category
,u.uld_type
,u.connection_type
,u.uld_no
,u.uld_no_of_bags
,uld_loading_progress.f_rush_bags(u.uld_no, u.flight_id) RUSH_BAGS -- CA00459
,u.uld_hold_no
,TO_CHAR(u.uld_load_time,'HH24:MI')
,SUBSTR(uld_loading_progress.f_get_first_bag_load1(u.uld_no, u.flight_id),1,5)
,SUBSTR(uld_loading_progress.f_get_last_bag_load1(u.uld_no, u.flight_id),1,5)
,SUBSTR(uld_loading_progress.f_get_last_bld_trace(u.uld_no, u.flight_id),1,5)
,uld_bag_weight(u.uld_no,f.flight_id) uld_weight
,uld_max_weight(u.uld_no,f.flight_id) max_uld_weight
,u.close_flag
,ut.username
,ut.trace_time
,u.volume_code
FROM ulds u
,uld_dests d
,flight f
,hold_v1 h1
,uld_trace_v1 ut
WHERE u.uld_no = d.uld_no
AND f.flight_id = u.flight_id
AND u.flight_id = d.flight_id
AND ut.uld_no (+) = u.uld_no
AND ut.trace_action (+) = 'ASSIGN'
AND ut.flight_id (+) = u.flight_id
AND h1.uld_no = u.uld_no
AND h1.flight_id = f.flight_id
AND (ut.trace_time = (SELECT MAX(ut2.trace_time)
                           FROM uld_trace_v1 ut2
                          WHERE ut2.uld_no = u.uld_no
                            AND ut2.flight_id = f.flight_id
                            AND ut2.trace_action = 'ASSIGN')
OR  h1.uld_no IN (SELECT hold_no FROM holds h))
ORDER BY h1.sort_key, h1.uld_no
;
