Untitled

WITH current_milestone AS (
select
  a.actual_date,
  ss.ID as shipment_id,
  ssm.MILESTONE_TYPE_ID,
  ssm.SEQUENCE
FROM
  BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT ss
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT_MILESTONE ssm ON ssm.SHIPMENT_ID = ss.id
  LEFT JOIN 
  ( SELECT 
      max(ssm.ACTUAL_DATE) as actual_date,
      max(ssm.id) as sequence,
      ss.id as shipment_id
    FROM
      BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT ss
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT_MILESTONE ssm ON ssm.SHIPMENT_ID = ss.id
    GROUP BY 3) a on a.shipment_id = ss.id and a.actual_date = ssm.ACTUAL_DATE and a.sequence = ssm.SEQUENCE
where a.actual_date is not null
group by 1,2,3,4),

delivered_milestone AS (
  SELECT
    ssm.ACTUAL_DATE as dm_date,
    ss.NAME,
    ss.CREATE_DATE,
    ss.ID as shipment_id
  FROM
    BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT ss
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT_MILESTONE ssm ON ssm.SHIPMENT_ID = ss.id
  WHERE
    ssm.MILESTONE_TYPE_ID = 18
)
SELECT
  so.NAME AS "Request Reference",
  sw.CODE AS "Shipping Nest",
  CASE
    WHEN so.PRIORITY = 0 THEN 'P3'
    WHEN so.PRIORITY = 1 THEN 'P2'
    WHEN so.PRIORITY = 2 THEN 'P1'
    ELSE 'P0'
  END AS "Priority",
  date_trunc('DAY', to_date(so.CREATE_DATE)) AS "Request Date",
  rp1.name AS "Requester",
  ---> potential problem ?
  rp2.NAME AS "Responsible Operator",
  ----> potential problem?
  CONCAT('[', p.DEFAULT_CODE, '] ', p.PRODUCT_NAME) AS "Part",
  spl.name AS "Serial Number",
  p.SYSTEM,
  p.LRU,
  ss.name AS "Shipment Number",
  ss2.name AS "Master Shipment",
  rp3.name AS "Freight Forwarder",
  ss.STATE AS "Shipment Status",
  ss.CREATE_DATE,
  CASE
    WHEN cm.MILESTONE_TYPE_ID = 1 THEN 'Shipment Booking'
    WHEN cm.MILESTONE_TYPE_ID = 2 THEN 'Scheduled Pickup'
    WHEN cm.MILESTONE_TYPE_ID = 5 THEN 'Departure at Origin'
    WHEN cm.MILESTONE_TYPE_ID = 6 THEN 'Trans-ship Port'
    WHEN cm.MILESTONE_TYPE_ID = 8 THEN '2nd Vessel'
    WHEN cm.MILESTONE_TYPE_ID = 10 THEN 'Port of Discharge'
    WHEN cm.MILESTONE_TYPE_ID = 12 THEN 'Customs Clearance At Port of Discharge'
    WHEN cm.MILESTONE_TYPE_ID = 14 THEN 'At Destination'
    WHEN cm.MILESTONE_TYPE_ID = 18 THEN 'Delivered'
    WHEN cm.MILESTONE_TYPE_ID = 19 THEN 'Port of Arrival'
    WHEN cm.MILESTONE_TYPE_ID = 20 THEN 'Port of Dest'
    WHEN cm.MILESTONE_TYPE_ID = 21 THEN 'Actual Pickup'
    WHEN cm.MILESTONE_TYPE_ID = 22 THEN 'Pickup'
    WHEN cm.MILESTONE_TYPE_ID = 24 THEN 'Port of Delivery'
    WHEN cm.MILESTONE_TYPE_ID = 25 THEN 'Empty delivered'
    WHEN cm.MILESTONE_TYPE_ID = 26 THEN 'Departure at Tranship Port'
    WHEN cm.MILESTONE_TYPE_ID = 29 THEN 'Customs Clearance at Port of Delivery'
    WHEN cm.MILESTONE_TYPE_ID = 30 THEN 'Pending Odoo Receipt due to discrepancies'
    WHEN cm.MILESTONE_TYPE_ID = 31 THEN 'Received at GCW/GH-1'
    WHEN cm.MILESTONE_TYPE_ID = 32 THEN 'Freight Picked up'
    WHEN cm.MILESTONE_TYPE_ID = 33 THEN 'Freight Arrival at Destination'
    WHEN cm.MILESTONE_TYPE_ID = 34 THEN 'Ready For Pickup'
    WHEN cm.MILESTONE_TYPE_ID = 35 THEN 'Available For Pickup'
    WHEN cm.MILESTONE_TYPE_ID = 37 THEN 'Returned to Shipper'
    WHEN cm.MILESTONE_TYPE_ID = 40 THEN 'Handed to FO'
  END AS "Current Milestone",
  cm.actual_date AS "Current Milestone Date",
  DATEDIFF(DAY, so.CREATE_DATE, dm.dm_date) as "Shipping Duration",
  CONCAT(
    'https://flyzipline.atlassian.net/browse/',
    so.CLIENT_ORDER_REF
  ) AS "OSD Link"
FROM
  --- ok lets find out more about the order request
  BIZ.DBT_STG.STG_ODOO_PROD__SALE_ORDER_LINE sol
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__SALE_ORDER so ON sol.ORDER_ID = so.id
  LEFT JOIN BIZ.DBT_ODOO.PRODUCTS p ON p.PRODUCT_ID = sol.PRODUCT_ID
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_WAREHOUSE sw ON so.WAREHOUSE_ID = sw.id --- now gimme the associated delivery if available, what's really/about to be shipped, how many and what's the serial
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE sm ON sol.id = sm.SALE_LINE_ID
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE_LINE sml ON sm.id = sml.MOVE_ID
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PRODUCTION_LOT spl ON sml.LOT_ID = spl.id --- is there a shipment? tell me more about it
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PICKING sp ON sm.PICKING_ID = sp.id
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT ss ON sp.SHIPMENT_ID = ss.id
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_PARTNER rp3 ON ss.FREIGHT_PARTNER_ID = rp3.id
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT ss2 ON ss.MASTER_SHIPMENT_ID = ss2.id -- ok who are the people requesting, tell me their names  
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_USERS ru1 ON so.USER_ID = ru1.ID
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_PARTNER rp1 ON ru1.PARTNER_ID = rp1.ID -- ok who are the people creating the order, tell me their names
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_USERS ru2 ON so.CREATE_UID = ru2.ID
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_PARTNER rp2 ON ru2.PARTNER_ID = rp2.ID
  LEFT JOIN delivered_milestone dm on dm.shipment_id = ss.id
  LEFT JOIN current_milestone cm on cm.shipment_id = ss.id
WHERE
  so.SO_TYPE_ID = 8
  and so.state not in ('cancel')
  and sm.state not in ('cancel')
ORDER BY so.CREATE_DATE DESC