CREATE OR REPLACE VIEW INBOUND_FLIGHT_LOOP_V2 AS
SELECT ca.agent_code,
       fii.flight_no,
       fii.flight_date,
       si.site_id,
       fii.trip_date
FROM contracted_agents ca,
     flight_info_inbound fii,
     site_info si
WHERE rtrim(ca.airline_alpha_code) = rtrim(substr(fii.flight_no,1,3))
AND to_number(substr(fii.flight_no,4,4)) >= ca.min_flight_no
AND to_number(substr(fii.flight_no,4,4)) <= ca.max_flight_no
AND (ca.flight_type = 'I' OR ca.flight_type = 'B')
AND si.airport_code = fii.airport_code
UNION
SELECT sa.agent_code,
       fii.flight_no,
       fii.flight_date,
       si.site_id,
       fii.trip_date
FROM super_agents sa,
     flight_info_inbound fii,
     site_info si
/* 
 * The Cartesian join in this select statement is intentional in order
 * to provide a list of all flights, with each super agent listed as the
 * agent_code.
 */
WHERE si.airport_code = fii.airport_code
/
