/*****************************************************************************
*
* Filename         - $Workfile:   reprint_uld_info_v1.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Design Spec
* Section          -
*
* Contents         - Live Schema View
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2000
*
*****************************************************************************/
PROMPT Creating view reprint_uld_info_v1

  CREATE OR REPLACE FORCE 
  VIEW reprint_uld_info_v1 (
  ULD_NO, 
  ULD_ID_CODE,
  DEST,
  LOADED_AT, 
  LOADED_AT_FLIGHT, 
  TRANSFER_AT, 
  TRANSFER_AT_FLIGHT, 
  ULD_CATEGORY, 
  ULD_TYPE, 
  FLIGHT_DATE, 
  CONTENTS_REMARKS, 
  REMARKS, 
  OFN_1, 
  OFD_1, 
  OFN_2, 
  OFD_2, 
  OFN_3, 
  OFD_3, 
  OFN_4, 
  OFD_4, 
  OFN_5, 
  OFD_5, 
  OFN_6, 
  OFD_6, 
  AIRCRAFT_REG, 
  WEIGHT, 
  VOLUME_CODE, 
  POSITION) 
AS   
SELECT
u.uld_no,
u.uld_id_code,
nvl (nvl( nvl( nvl( nvl(ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 6), ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 5)) , ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 4)), ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 3)), ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 2)), ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 1)),
s.airport_code,
f.flight_no,
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 1),
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 1),
u.uld_category,
u.uld_type,
f.flight_date,
d.contents_remarks,
d.remarks,
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 1),
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 1),
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 2),
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 2),
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 3),
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 3),
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 4),
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 4),
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 5),
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 5),
ulds_pkg.get_onward_uld_flight_no(u.uld_no, f.flight_id, 6),
ulds_pkg.get_onward_uld_dest(u.uld_no, f.flight_id, 6),
fi.aircraft_registration,
uld_bag_weight(u.uld_no, f.flight_id),
u.volume_code,
u.uld_hold_no
FROM
ulds u,
uld_card_details d,
uld_recorded_id_codes uric,
site_info s,
flight f,
flight_info fi
WHERE uric.uld_id_code (+)= u.uld_id_code 
AND d.template_name (+)= uric.template_name 
AND d.card_id (+)= uric.card_id
AND s.site_id = f.site_id 
AND f.flight_id = u.flight_id 
AND trim(fi.flight_no (+))= trim(f.flight_no)
AND fi.flight_date (+)= f.flight_date;