WITH branch AS (SELECT location_id, business_unit, service_hub
FROM tms.tms_location
WHERE business_unit IS NOT NULL),
base
AS (SELECT s.stop_id,
h.truck_route_id,
s.truck_route_instance_id,
s.scheduled_arrival_dt,
t.business_unit hub_branch_unit,
SIGN(30
- (s.first_scan_time - s.scheduled_arrival_dt)
* 1440)
on_time,
TO_CHAR (TRUNC (h.scheduled_departure_date, 'MONTH'),
'YYYY-MM-DD')
calc_date
-- notes:if an exception stop, first_scan_time will be null, which will be considered as 'Late'
FROM tms_route_stop s
INNER JOIN
tms_route_header h
ON s.truck_route_instance_id =
h.truck_route_instance_id
INNER JOIN
tms.tms_location t
ON h.dispatch_location_id = t.location_id
--notes:for sub route, this will be the hub/branch that parent route starts from.
INNER JOIN
branch
ON s.location_id = branch.location_id
WHERE h.download_exempt <> 'Y'
AND s.scheduled_arrival_dt IS NOT NULL
-- notes:If there is no schedule date, we will not count them in
AND h.end_of_day IS NOT NULL
AND s.is_blank_stop = 'N'
AND h.scheduled_departure_date >=
TO_DATE (:start_date, 'MM/DD/YYYY')
AND h.scheduled_departure_date <
TO_DATE (:end_date, 'MM/DD/YYYY')
AND TRUNC (h.scheduled_departure_date) <=
TRUNC (SYSDATE - 2)
AND (:hub_code IS NULL OR t.business_unit = :hub_code)),
complete_vw
AS ( SELECT rpt1.hub_branch_unit,
rpt1.calc_date,
COUNT (DISTINCT rpt1.truck_route_instance_id)
total_routes,
COUNT (1) total_stops,
ROUND (
100
* SUM (DECODE (on_time, -1, 0, on_time))
/ COUNT (1),
2
)
ontime_percent,
0 exempt_routes,
0 incomplete_routes,
COUNT (DISTINCT rpt1.truck_route_instance_id) extra_count
FROM base rpt1
GROUP BY rpt1.hub_branch_unit, rpt1.calc_date),
exempt_vw
AS ( SELECT hub_branch_unit,
calc_date,
0 total_routes,
0 total_stops,
0 ontime_percent,
COUNT (truck_route_instance_id) exempt_routes,
0 incomplete_routes,
COUNT (truck_route_instance_id) extra_count
FROM (SELECT h.truck_route_instance_id,
TO_CHAR (
TRUNC (h.scheduled_departure_date, 'MONTH'),
'YYYY-MM-DD'
)
calc_date,
hub.business_unit hub_branch_unit
FROM tms_route_header h, tms.tms_location hub
WHERE h.dispatch_location_id = hub.location_id
AND h.download_exempt = 'Y'
AND h.scheduled_departure_date >=
TO_DATE (:start_date, 'MM/DD/YYYY')
AND h.scheduled_departure_date <
TO_DATE (:end_date, 'MM/DD/YYYY')
AND TRUNC (h.scheduled_departure_date) <=
TRUNC (SYSDATE - 2)
AND EXISTS
(SELECT NULL
FROM tms.tms_route_stop tstp,
tms.tms_location l
WHERE h.truck_route_instance_id =
tstp.truck_route_instance_id
AND tstp.location_id =
l.location_id
AND l.business_unit IS NOT NULL
AND l.location_type = 'BRANCH'))
GROUP BY hub_branch_unit, calc_date),
incomplete_vw
AS ( SELECT hub_branch_unit,
calc_date,
0 total_routes,
0 total_stops,
0 ontime_percent,
0 exempt_routes,
COUNT (truck_route_instance_id) incomplete_routes,
COUNT (truck_route_instance_id) extra_count
FROM (SELECT h.truck_route_instance_id,
TO_CHAR (
TRUNC (h.scheduled_departure_date, 'MONTH'),
'YYYY-MM-DD'
)
calc_date,
hub.business_unit hub_branch_unit
FROM tms_route_header h, tms.tms_location hub
WHERE h.dispatch_location_id = hub.location_id
AND h.download_exempt = 'N'
AND h.scheduled_departure_date >=
TO_DATE (:start_date, 'MM/DD/YYYY')
AND h.scheduled_departure_date <
TO_DATE (:end_date, 'MM/DD/YYYY')
AND TRUNC (h.scheduled_departure_date) <=
TRUNC (SYSDATE - 2)
AND h.end_of_day IS NULL)
GROUP BY hub_branch_unit, calc_date),
dai
AS (SELECT TO_CHAR (route_date, 'YYYY-MM-DD') range_name, route_date
FROM ( SELECT end_date - LEVEL + 1 route_date
FROM (SELECT TO_DATE (:start_date, 'MM/DD/YYYY')
start_date,
TO_DATE (:end_date, 'MM/DD/YYYY')
end_date
FROM DUAL)
CONNECT BY LEVEL <= end_date - start_date + 1)
WHERE route_date <= TRUNC (SYSDATE - 2)),
device_byday
AS (SELECT alh.hub5,
alh.allocate_date,
dai.route_date,
dai.range_name,
TO_CHAR (TRUNC (dai.route_date, 'MONTH'), 'YYYY-MM-DD')
route_month,
ROW_NUMBER ()
OVER (
PARTITION BY alh.inv_item_id,
alh.mnf_serial,
dai.range_name
ORDER BY alh.allocate_date DESC
)
rn
FROM tms.tms_device_usage alh
INNER JOIN
dai
ON alh.allocate_date < dai.route_date + 1),
device_usage
AS ( SELECT hub5, route_month, ROUND (AVG (target), 2) device_usage
FROM ( SELECT hub5,
route_month,
route_date,
COUNT ( * ) target
FROM (SELECT *
FROM device_byday
WHERE rn = 1)
GROUP BY hub5, route_month, route_date)
GROUP BY hub5, route_month)
SELECT route.hub_branch_unit,
route.calc_date,
route.total_routes,
route.total_stops,
route.ontime_percent,
route.exempt_routes,
route.incomplete_routes,
route.extra_count,
NVL (device.device_usage, 0) sumval
FROM ( SELECT hub_branch_unit,
calc_date,
SUM (total_routes) total_routes,
SUM (total_stops) total_stops,
DECODE (SUM (total_routes),
0, NULL,
SUM (ontime_percent))
ontime_percent,
SUM (exempt_routes) exempt_routes,
SUM (incomplete_routes) incomplete_routes,
SUM (extra_count) extra_count
FROM ( SELECT * FROM complete_vw
UNION ALL
SELECT * FROM exempt_vw
UNION ALL
SELECT * FROM incomplete_vw) vw
GROUP BY hub_branch_unit, calc_date) route
LEFT JOIN
device_usage device
ON route.hub_branch_unit = device.hub5
AND route.calc_date = device.route_month
ORDER BY route.hub_branch_unit, calc_date