PROMPT Creating view recorded_uld_info_v1

CREATE OR REPLACE FORCE VIEW recorded_uld_info_v1
(
    uld_id_code
  , uld_no
  , 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
)
AS
SELECT 
      a.uld_id_code
     , a.uld_no
     , decode (a.uld_category, 'S', 'SORT',  Nvl(a.dest6,nvl (a.dest5, nvl (a.dest4, nvl (a.dest3, nvl (a.dest2, a.dest1)))))) DEST
     , b.loaded_at
     , a.flight_no        LOADED_AT_FLIGHT
     , b.transfer_at
     , a.onward_flight_no1 TRANSFER_AT_FLIGHT
     , a.uld_category
     , a.uld_type
     , a.flight_date
     , a.contents_remarks
     , a.remarks
     , a.onward_flight_no1 OFN_1
     , a.dest1 OFD_1
     , a.onward_flight_no2 OFN_2
     , a.dest2 OFD_2
     , a.onward_flight_no3 OFN_3
     , a.dest3 OFD_3
     , a.onward_flight_no4 OFN_4
     , a.dest4 OFD_4
     , a.onward_flight_no5 OFN_5
     , a.dest5 OFD_5
     , a.onward_flight_no6 OFN_6
     , a.dest6 OFD_6
     , fi.aircraft_registration
FROM uld_recorded_id_codes a
   , uld_card_details b
   , flight_info fi
WHERE a.template_name = b.template_name
AND   a.agent         = b.agent
AND   a.card_id       = b.card_id
AND   trim(fi.flight_no(+)) = trim(a.flight_no) 
AND   fi.flight_date (+)= a.flight_date 
ORDER BY uld_id_code;

