Welcome
Click Update Data to upload your CSV export.
Click Update Data to upload your CSV export.
Enter daily planned volume for Non-Sort per site. These values are used across all windows.
T+1 Capacity Validation — coming soon.
This section will replicate the Midwest T+1 Capacity Validator with per-site views, multi-CSV uploads, and weekly planning tools.
WITH actuals_detail AS (
-- Inbound Total (100004, 100032, 100360, 100359)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'transin' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id IN (100004, 100032, 100360, 100359)
GROUP BY 1, 2, 3, 4
UNION ALL
-- Non-Sort Actuals (100032 only - drill-in subset of Inbound)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'nonsort' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100032
GROUP BY 1, 2, 3, 4
UNION ALL
-- Ship/Outbound (100131 only)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'outbound sd' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100131
GROUP BY 1, 2, 3, 4
UNION ALL
-- ICQA Total (100017, 100026, 100112)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'icqa count' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id IN (100017, 100026, 100112)
GROUP BY 1, 2, 3, 4
UNION ALL
-- Sort Total (from regular sites - process_id 100021)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'sortation' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('SOH1', 'SOH2', 'SOH3', 'SIN9', 'SKY2', 'SPA5')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100021
GROUP BY 1, 2, 3, 4
UNION ALL
-- Sort Total (from V-sites mapped to regular sites - process_id 100021)
SELECT
CASE
WHEN i.warehouse_id = 'VOH1' THEN 'SOH1'
WHEN i.warehouse_id = 'VOH2' THEN 'SOH2'
WHEN i.warehouse_id = 'VOH3' THEN 'SOH3'
WHEN i.warehouse_id = 'VIN9' THEN 'SIN9'
WHEN i.warehouse_id = 'VKY2' THEN 'SKY2'
WHEN i.warehouse_id = 'VPA5' THEN 'SPA5'
ELSE i.warehouse_id
END as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'sortation' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('VOH1', 'VOH2', 'VOH3', 'VIN9', 'VKY2', 'VPA5')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100021
GROUP BY 1, 2, 3, 4
),
actuals AS (
SELECT
site,
date,
window,
SUM(CASE WHEN department = 'transin' THEN total_units END) as ib_units,
SUM(CASE WHEN department = 'transin' THEN total_eaches END) as ib_eaches,
SUM(CASE WHEN department = 'transin' THEN total_hours END) as ib_hours,
SUM(CASE WHEN department = 'nonsort' THEN total_units END) as nonsort_units,
SUM(CASE WHEN department = 'nonsort' THEN total_eaches END) as nonsort_eaches,
SUM(CASE WHEN department = 'nonsort' THEN total_hours END) as nonsort_hours,
SUM(CASE WHEN department = 'outbound sd' THEN total_units END) as ob_units,
SUM(CASE WHEN department = 'outbound sd' THEN total_eaches END) as ob_eaches,
SUM(CASE WHEN department = 'outbound sd' THEN total_hours END) as ob_hours,
SUM(CASE WHEN department = 'sortation' THEN total_units END) as sort_units,
SUM(CASE WHEN department = 'sortation' THEN total_eaches END) as sort_eaches,
SUM(CASE WHEN department = 'sortation' THEN total_hours END) as sort_hours,
SUM(CASE WHEN department = 'icqa count' THEN total_units END) as icqa_units,
SUM(CASE WHEN department = 'icqa count' THEN total_eaches END) as icqa_eaches,
SUM(CASE WHEN department = 'icqa count' THEN total_hours END) as icqa_hours
FROM actuals_detail
GROUP BY 1, 2, 3
),
planning AS (
SELECT
CASE
WHEN lp_id = -15000149 THEN 'SOH2'
WHEN lp_id = -295234656514 THEN 'SPA5'
WHEN lp_id = -15000148 THEN 'SOH1'
WHEN lp_id = -15000192 THEN 'SOH3'
WHEN lp_id = -15000165 THEN 'SIN9'
WHEN lp_id = -295234637505 THEN 'SKY2'
ELSE 'Unknown'
END as site,
DATE(operations_date) as date,
CASE
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 6 THEN 'Lunch'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 0 THEN 'Brunch'
END as window,
SUM(CASE WHEN process_path = 'transin' THEN volume_original_value END) as ib_vol_orig,
SUM(CASE WHEN process_path = 'transin' THEN volume_override_value END) as ib_vol_override,
SUM(CASE WHEN process_path = 'transin' THEN labor_hours_original_value END) as ib_hrs_orig,
SUM(CASE WHEN process_path = 'transin' THEN labor_hours_override_value END) as ib_hrs_override,
SUM(CASE WHEN process_path = 'outbound sd' THEN volume_original_value END) as ob_vol_orig,
SUM(CASE WHEN process_path = 'outbound sd' THEN volume_override_value END) as ob_vol_override,
SUM(CASE WHEN process_path = 'outbound sd' THEN labor_hours_original_value END) as ob_hrs_orig,
SUM(CASE WHEN process_path = 'outbound sd' THEN labor_hours_override_value END) as ob_hrs_override,
SUM(CASE WHEN process_path = 'sortation' THEN volume_original_value END) as sort_vol_orig,
SUM(CASE WHEN process_path = 'sortation' THEN volume_override_value END) as sort_vol_override,
SUM(CASE WHEN process_path = 'sortation' THEN labor_hours_original_value END) as sort_hrs_orig,
SUM(CASE WHEN process_path = 'sortation' THEN labor_hours_override_value END) as sort_hrs_override,
SUM(CASE WHEN process_path = 'icqa count' THEN volume_original_value END) as icqa_vol_orig,
SUM(CASE WHEN process_path = 'icqa count' THEN volume_override_value END) as icqa_vol_override,
SUM(CASE WHEN process_path = 'icqa count' THEN labor_hours_original_value END) as icqa_hrs_orig,
SUM(CASE WHEN process_path = 'icqa count' THEN labor_hours_override_value END) as icqa_hrs_override
FROM "andes"."GSF-Central"."labor_demand_outbound_aggregate"
WHERE operations_date >= CURRENT_DATE - 7
AND operations_date <= CURRENT_DATE + 7
AND lp_id IN (-15000149, -295234656514, -15000148, -15000192, -15000165, -295234637505)
GROUP BY 1, 2, 3
)
SELECT
COALESCE(a.site, p.site) as site,
COALESCE(a.date, p.date) as date,
COALESCE(a.window, p.window) as window,
-- Inbound Actuals
a.ib_units as ib_actual_units,
a.ib_eaches as ib_actual_eaches,
a.ib_hours as ib_actual_hours,
-- Inbound Planning
p.ib_vol_orig as ib_plan_volume_original,
p.ib_vol_override as ib_plan_volume_override,
p.ib_hrs_orig as ib_plan_hours_original,
p.ib_hrs_override as ib_plan_hours_override,
-- Outbound Actuals
a.ob_units as ob_actual_units,
a.ob_eaches as ob_actual_eaches,
a.ob_hours as ob_actual_hours,
-- Outbound Planning
p.ob_vol_orig as ob_plan_volume_original,
p.ob_vol_override as ob_plan_volume_override,
p.ob_hrs_orig as ob_plan_hours_original,
p.ob_hrs_override as ob_plan_hours_override,
-- Sort Actuals
a.sort_units as sort_actual_units,
a.sort_eaches as sort_actual_eaches,
a.sort_hours as sort_actual_hours,
-- Sort Planning
p.sort_vol_orig as sort_plan_volume_original,
p.sort_vol_override as sort_plan_volume_override,
p.sort_hrs_orig as sort_plan_hours_original,
p.sort_hrs_override as sort_plan_hours_override,
-- ICQA Actuals
a.icqa_units as icqa_actual_units,
a.icqa_eaches as icqa_actual_eaches,
a.icqa_hours as icqa_actual_hours,
-- ICQA Planning
p.icqa_vol_orig as icqa_plan_volume_original,
p.icqa_vol_override as icqa_plan_volume_override,
p.icqa_hrs_orig as icqa_plan_hours_original,
p.icqa_hrs_override as icqa_plan_hours_override,
-- Non-Sort Actuals (100032 only - drill-in subset of Inbound)
a.nonsort_units as nonsort_actual_units,
a.nonsort_eaches as nonsort_actual_eaches,
a.nonsort_hours as nonsort_actual_hours
FROM actuals a
FULL OUTER JOIN planning p
ON a.site = p.site
AND a.date = p.date
AND a.window = p.window
ORDER BY site, date, window
;