Transit Times, Including Location and Geography Information.sql

SELECT
	wtt.TRANSIT_TIME_ID
  , wtt.TRANSIT_TIME_CODE
  , wtt.ORIGIN_TYPE
  , wtt.DESTINATION_TYPE
  ,
	(
		Case wtt.ORIGIN_TYPE
			when 'LE'
				then
				(
					select
						ADDRESS1 || ',' || ADDRESS2 || ',' || ADDRESS3 || ',' || ADDRESS4 || ',' || CITY || ',' || POSTAL_CODE || ',' || STATE || ',' || PROVINCE || ',' || COUNTY || ',' || Country
					from
						fusion.hz_locations
					where
						location_id = wtt.FROM_LOCATION_ID
				)
				else
					 (
						 select
							 INTERNAL_LOCATION_CODE
						 from
							 fusion.HR_LOCATIONS_ALL_F hla
						 WHERE
							 hla.location_id =wtt.FROM_LOCATION_ID
							 and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
				)
		end
	)
	as FROM_LOCATION
  ,
	(
		Case wtt.DESTINATION_TYPE
			when 'LE'
				then
				(
					select
						ADDRESS1 || ',' || ADDRESS2 || ',' || ADDRESS3 || ',' || ADDRESS4 || ',' || CITY || ',' || POSTAL_CODE || ',' || STATE || ',' || PROVINCE || ',' || COUNTY || ',' || Country
					from
						fusion.hz_locations
					where
						location_id = wtt.TO_LOCATION_ID
				)
				else
					 (
						 select
							 INTERNAL_LOCATION_CODE
						 from
							 fusion.HR_LOCATIONS_ALL_F hla
						 WHERE
							 hla.location_id =wtt.TO_LOCATION_ID
							 and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
				)
		end
	)
	as TO_LOCATION
  , (
		SELECT
			geography_name
		FROM
			fusion.hz_geographies
		WHERE
			geography_id =wtt.FROM_REGION_ID
	)
	AS FROM_REGION_ID
  , (
		SELECT
			geography_name
		FROM
			fusion.hz_geographies
		WHERE
			geography_id = wtt.TO_REGION_ID
	)
	AS TO_REGION_ID
  , (
		SELECT
			geography_name
		FROM
			fusion.hz_geographies
		WHERE
			geography_id = wtt.FROM_ZONE_ID
	)
	AS FROM_ZONE_ID
  , (
		SELECT
			geography_name
		FROM
			fusion.hz_geographies
		WHERE
			geography_id = wtt.TO_ZONE_ID
	)
	AS TO_ZONE_ID
  , wttv.*
FROM
	FUSION.WSH_TRANSIT_TIMEs       wtt
  , fusion.WSH_TRANSIT_TIME_VALUES wttv
where
	wtt.TRANSIT_TIME_ID = wttv.transit_time_id
order by
	5
  , 7