Fulfillment Strategy

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Fulfillment Strategy per fulfillment unit (order line or split portion of a line),
-- sourced from the order management / orchestration backend and the ERP location master.
WITH order_lines AS (
  SELECT
    CAST(ol.order_id AS STRING) AS order_id,
    CAST(ol.order_line_id AS STRING) AS order_line_id,
    ol.sales_channel,
    ol.order_created_timestamp,
    UPPER(ol.line_status) AS line_status
  FROM `your-project.oms.order_lines` AS ol
  WHERE DATE(ol.order_created_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY ol.order_id, ol.order_line_id ORDER BY ol.updated_timestamp DESC) = 1
),
locations AS (
  SELECT
    loc.location_id,
    UPPER(loc.location_type) AS location_type
  FROM `your-project.erp.locations` AS loc
  QUALIFY ROW_NUMBER() OVER (PARTITION BY loc.location_id ORDER BY loc.updated_timestamp DESC) = 1
),
assignments AS (
  -- one row per allocation / reallocation of a fulfillment unit
  SELECT
    CAST(fa.order_id AS STRING) AS order_id,
    CAST(fa.order_line_id AS STRING) AS order_line_id,
    CAST(fa.fulfillment_unit_id AS STRING) AS fulfillment_unit_id,
    fa.assigned_node_id,
    fa.assignment_timestamp,
    CASE
      WHEN UPPER(fa.delivery_method) IN ('CURBSIDE', 'CURBSIDE_PICKUP') THEN 'Curbside Pickup'
      WHEN UPPER(fa.delivery_method) IN ('PICKUP', 'STORE_PICKUP', 'BOPIS') AND l.location_type = 'STORE' THEN 'BOPIS'
      WHEN UPPER(fa.delivery_method) IN ('PICKUP', 'STORE_PICKUP', 'SHIP_TO_STORE', 'BOSS')
        AND l.location_type IN ('DC', 'DISTRIBUTION_CENTER', 'WAREHOUSE', 'FULFILLMENT_CENTER') THEN 'Ship to Store'
      WHEN UPPER(fa.fulfillment_provider_type) IN ('DROP_SHIP', 'DROPSHIP', 'VENDOR') THEN 'Drop Ship'
      WHEN UPPER(fa.fulfillment_provider_type) IN ('3PL', 'THIRD_PARTY', 'MARKETPLACE') THEN 'Third-Party Fulfillment'
      WHEN UPPER(fa.delivery_method) IN ('SAME_DAY_DELIVERY', 'LOCAL_DELIVERY') AND l.location_type = 'STORE' THEN 'Same-Day Delivery from Store'
      WHEN l.location_type = 'STORE' THEN 'Ship from Store'
      WHEN l.location_type IN ('DC', 'DISTRIBUTION_CENTER', 'WAREHOUSE', 'FULFILLMENT_CENTER') THEN 'Ship from DC'
      ELSE 'Other'
    END AS fulfillment_strategy,
    ROW_NUMBER() OVER (PARTITION BY fa.fulfillment_unit_id ORDER BY fa.assignment_timestamp ASC) AS first_rank,
    ROW_NUMBER() OVER (PARTITION BY fa.fulfillment_unit_id ORDER BY fa.assignment_timestamp DESC) AS last_rank
  FROM `your-project.oms.fulfillment_assignments` AS fa
  LEFT JOIN locations AS l
    ON l.location_id = fa.assigned_node_id
),
units AS (
  SELECT
    order_id,
    order_line_id,
    fulfillment_unit_id,
    COUNT(*) AS assignment_count,
    MAX(IF(first_rank = 1, fulfillment_strategy, NULL)) AS initial_fulfillment_strategy,
    MAX(IF(last_rank = 1, fulfillment_strategy, NULL)) AS current_fulfillment_strategy,
    MAX(IF(last_rank = 1, assigned_node_id, NULL)) AS fulfillment_node_id,
    MAX(IF(last_rank = 1, assignment_timestamp, NULL)) AS strategy_effective_timestamp
  FROM assignments
  GROUP BY order_id, order_line_id, fulfillment_unit_id
)
SELECT
  ol.order_id,
  ol.order_line_id,
  COALESCE(u.fulfillment_unit_id, CONCAT(ol.order_id, '-', ol.order_line_id)) AS fulfillment_unit_id,
  DATE(ol.order_created_timestamp) AS order_date,
  ol.sales_channel,
  COALESCE(u.initial_fulfillment_strategy, 'Unassigned') AS initial_fulfillment_strategy,
  COALESCE(u.current_fulfillment_strategy, 'Unassigned') AS fulfillment_strategy,
  u.fulfillment_node_id,
  u.strategy_effective_timestamp,
  COALESCE(u.initial_fulfillment_strategy != u.current_fulfillment_strategy, FALSE) AS is_strategy_changed,
  COALESCE(u.assignment_count, 0) > 1 AS is_rerouted,
  COUNT(u.fulfillment_unit_id) OVER (PARTITION BY ol.order_id, ol.order_line_id) > 1 AS is_split_line,
  ol.line_status IN ('CANCELLED', 'CANCELED') AS is_cancelled
FROM order_lines AS ol
LEFT JOIN units AS u
  ON u.order_id = ol.order_id
 AND u.order_line_id = ol.order_line_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Fulfillment Strategy per fulfillment unit from the order management backend datasets
-- (oms_order_lines, oms_fulfillment_assignments) and the ERP location master (erp_locations) ingested into AEP.
WITH order_lines AS (
  SELECT order_id, order_line_id, sales_channel, order_created_timestamp, line_status
  FROM (
    SELECT
      CAST(ol.order_id AS STRING) AS order_id,
      CAST(ol.order_line_id AS STRING) AS order_line_id,
      ol.sales_channel,
      ol.order_created_timestamp,
      UPPER(ol.line_status) AS line_status,
      ROW_NUMBER() OVER (PARTITION BY ol.order_id, ol.order_line_id ORDER BY ol.updated_timestamp DESC) AS rn
    FROM oms_order_lines AS ol
    WHERE CAST(ol.order_created_timestamp AS DATE) >= DATE_SUB(CURRENT_DATE, 90)
  ) AS x
  WHERE rn = 1
),
locations AS (
  SELECT location_id, location_type
  FROM (
    SELECT
      loc.location_id,
      UPPER(loc.location_type) AS location_type,
      ROW_NUMBER() OVER (PARTITION BY loc.location_id ORDER BY loc.updated_timestamp DESC) AS rn
    FROM erp_locations AS loc
  ) AS y
  WHERE rn = 1
),
assignments AS (
  SELECT
    CAST(fa.order_id AS STRING) AS order_id,
    CAST(fa.order_line_id AS STRING) AS order_line_id,
    CAST(fa.fulfillment_unit_id AS STRING) AS fulfillment_unit_id,
    fa.assigned_node_id,
    fa.assignment_timestamp,
    CASE
      WHEN UPPER(fa.delivery_method) IN ('CURBSIDE', 'CURBSIDE_PICKUP') THEN 'Curbside Pickup'
      WHEN UPPER(fa.delivery_method) IN ('PICKUP', 'STORE_PICKUP', 'BOPIS') AND l.location_type = 'STORE' THEN 'BOPIS'
      WHEN UPPER(fa.delivery_method) IN ('PICKUP', 'STORE_PICKUP', 'SHIP_TO_STORE', 'BOSS')
        AND l.location_type IN ('DC', 'DISTRIBUTION_CENTER', 'WAREHOUSE', 'FULFILLMENT_CENTER') THEN 'Ship to Store'
      WHEN UPPER(fa.fulfillment_provider_type) IN ('DROP_SHIP', 'DROPSHIP', 'VENDOR') THEN 'Drop Ship'
      WHEN UPPER(fa.fulfillment_provider_type) IN ('3PL', 'THIRD_PARTY', 'MARKETPLACE') THEN 'Third-Party Fulfillment'
      WHEN UPPER(fa.delivery_method) IN ('SAME_DAY_DELIVERY', 'LOCAL_DELIVERY') AND l.location_type = 'STORE' THEN 'Same-Day Delivery from Store'
      WHEN l.location_type = 'STORE' THEN 'Ship from Store'
      WHEN l.location_type IN ('DC', 'DISTRIBUTION_CENTER', 'WAREHOUSE', 'FULFILLMENT_CENTER') THEN 'Ship from DC'
      ELSE 'Other'
    END AS fulfillment_strategy,
    ROW_NUMBER() OVER (PARTITION BY fa.fulfillment_unit_id ORDER BY fa.assignment_timestamp ASC) AS first_rank,
    ROW_NUMBER() OVER (PARTITION BY fa.fulfillment_unit_id ORDER BY fa.assignment_timestamp DESC) AS last_rank
  FROM oms_fulfillment_assignments AS fa
  LEFT JOIN locations AS l
    ON l.location_id = fa.assigned_node_id
),
units AS (
  SELECT
    order_id,
    order_line_id,
    fulfillment_unit_id,
    COUNT(*) AS assignment_count,
    MAX(CASE WHEN first_rank = 1 THEN fulfillment_strategy END) AS initial_fulfillment_strategy,
    MAX(CASE WHEN last_rank = 1 THEN fulfillment_strategy END) AS current_fulfillment_strategy,
    MAX(CASE WHEN last_rank = 1 THEN assigned_node_id END) AS fulfillment_node_id,
    MAX(CASE WHEN last_rank = 1 THEN assignment_timestamp END) AS strategy_effective_timestamp
  FROM assignments
  GROUP BY order_id, order_line_id, fulfillment_unit_id
)
SELECT
  ol.order_id,
  ol.order_line_id,
  COALESCE(u.fulfillment_unit_id, CONCAT(ol.order_id, '-', ol.order_line_id)) AS fulfillment_unit_id,
  CAST(ol.order_created_timestamp AS DATE) AS order_date,
  ol.sales_channel,
  COALESCE(u.initial_fulfillment_strategy, 'Unassigned') AS initial_fulfillment_strategy,
  COALESCE(u.current_fulfillment_strategy, 'Unassigned') AS fulfillment_strategy,
  u.fulfillment_node_id,
  u.strategy_effective_timestamp,
  COALESCE(u.initial_fulfillment_strategy <> u.current_fulfillment_strategy, FALSE) AS is_strategy_changed,
  COALESCE(u.assignment_count, 0) > 1 AS is_rerouted,
  COUNT(u.fulfillment_unit_id) OVER (PARTITION BY ol.order_id, ol.order_line_id) > 1 AS is_split_line,
  ol.line_status IN ('CANCELLED', 'CANCELED') AS is_cancelled
FROM order_lines AS ol
LEFT JOIN units AS u
  ON u.order_id = ol.order_id
 AND u.order_line_id = ol.order_line_id;