share

WITH NET AS (
  SELECT
    inv.PRODUCT_ID,
    inv.QUANTITY,
    inv.RESERVED_QUANTITY,
    inv.WAREHOUSE_ID,
    loc.STOCK_LOCATION_ID
  FROM
    BIZ.DBT_STG.STG_ODOO_PROD__STOCK_QUANT inv
    INNER JOIN BIZ.DBT_ODOO.LOCATIONS loc ON LOC.STOCK_LOCATION_ID = inv.LOCATION_ID
  WHERE
    loc.PARENT_PATH LIKE '%1/3571/5925/%'
    OR loc.PARENT_PATH LIKE '%1/3571/5894/%'
    OR loc.PARENT_PATH LIKE '%1/6216/6217/%'
),
NETSUM AS (
  SELECT
    x.PRODUCT_ID,
    SUM(x.QUANTITY) AS what,
    SUM(x.reserved_quantity) AS how,
    MAX(x.warehouse_id) AS WH
  FROM
    NET x
  GROUP BY
    X.PRODUCT_ID
),
Final AS( Select
  so.CREATE_DATE AS Requested_Date,
  so.COMMITMENT_DATE, 
  --- benson's changes --------------------
  ----- added case statements for SOs in pending, draft or cancel
  CASE
    WHEN so.state IN ('pending','draft','cancel') THEN so.NAME
    ELSE sm.ORIGIN
  END AS SO,
  CASE
    WHEN so.state IN ('pending','draft','cancel') THEN CONCAT('[',prod2.DEFAULT_CODE,']',' ',prod2.PRODUCT_NAME)
    ELSE CONCAT('[',prod.DEFAULT_CODE,']',' ',prod.PRODUCT_NAME)
  END AS PART,
  CASE
    WHEN so.state IN ('pending','draft','cancel') THEN sol.PRODUCT_UOM_QTY
    ELSE sm.PRODUCT_QTY 
  END AS QTY,
  CASE
    WHEN so.state IN ('pending','draft','cancel') THEN rp2.display_name
    ELSE rp.display_name 
  END AS Requesting_Nest,
  ---- end of change --------------------
  so.STATE,
  CASE 
  When so.STATE = 'pending' then 'waiting approval'
  ------ added 'sale' and 'draft' in case statements----
  when so.state in ('done','sale') then 'approved'
  when so.state = 'draft' then 'not submitted'
  ---- end of change --------------------
  when so.state = 'cancel' then 'cancelled'
  End as Approval
  ,
  CASE
  WHEN sp.PICKING_TYPE_ID IN ('289', '302', '123', '301')
      AND sp.STATE = 'confirmed'
      AND WH != so.ID THEN 'Incorrect release warehouse'
      WHEN sp.STATE = 'confirmed'
      AND sp.PICKING_TYPE_ID IN ('289', '302', '123', '301')
      AND what IS NULL THEN 'Out of Stock'
      WHEN sp.STATE = 'confirmed'
      AND sp.PICKING_TYPE_ID IN ('289', '302', '123', '301')
      AND what = 0 THEN 'Out of Stock'
      WHEN sp.PICKING_TYPE_ID IN ('289', '302', '123', '301')
      AND sp.STATE = 'confirmed'
      AND what != 0
      AND what = how
      AND Wh = So.id THEN 'Unavailable to Reserve'
      WHEN sp.STATE = 'assigned'
      AND sp.PICKING_TYPE_ID IN ('289', '302', '123', '301') THEN 'Ready to Pick'
      WHEN sp.STATE = 'assigned'
      AND prc.name = 'United States'
      AND sp.PICKING_TYPE_ID IN ('474') THEN 'Pending Conformity'
      WHEN sp.STATE = 'waiting'
      AND sp.PICKING_TYPE_ID IN ('289', '302', '123', '301') THEN 'ODOO Error'
      WHEN sp.STATE = 'partially_available'
      AND sp.PICKING_TYPE_ID IN ('289', '302', '123', '301') THEN 'Ready to Pick'
      WHEN sp.STATE = 'assigned'
      AND sp.PICKING_TYPE_ID = 124 THEN 'Ready to Pack'
      WHEN sp.STATE = 'waiting'
      AND sp.PICKING_TYPE_ID = 124 THEN 'Awaiting Pick'
      WHEN sp.STATE = 'confirmed'
      AND sp.PICKING_TYPE_ID = 124 THEN 'ODOO Error'
      WHEN sp.STATE = 'partially_available'
      AND sp.PICKING_TYPE_ID = 124 THEN 'Ready to Pack'
      WHEN sp.STATE = 'assigned'
      AND sp.PICKING_TYPE_ID = '122' THEN 'Ready to Ship'
      WHEN sp.STATE = 'waiting'
      AND sp.PICKING_TYPE_ID = 122 THEN 'Awaiting Pack'
      WHEN sp.STATE = 'confirmed'
      AND sp.PICKING_TYPE_ID = 122 THEN 'ODOO Error'
      WHEN sp.STATE = 'partially_available'
      AND sp.PICKING_TYPE_ID = 122 THEN 'Ready to Ship'
      WHEN sp.state ='done' 
      And sp.PICKING_TYPE_ID =122 Then 'Delivered'
    END AS STATUS
  
  FROM
   BIZ.DBT_STG.STG_ODOO_PROD__SALE_ORDER so
   --- benson's changes --------------------
   --- added new joins to show data for orders that are not yet confirmed
    JOIN BIZ.DBT_STG.STG_ODOO_PROD__SALE_ORDER_LINE sol ON so.id = sol.ORDER_ID
    JOIN BIZ.DBT_ODOO.PRODUCTS Prod2 ON prod2.PRODUCT_ID = sol.product_id
    JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_PARTNER rp2 ON rp2.id = so.PARTNER_ID
    --- changed joins from INNER to LEFT JOINS, because stock_move records dont exist if order is draft or submitted
    LEFT Join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE sm on sm.SALE_ID = so.ID
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_PICKING sp ON sp.ID = sm.PICKING_ID
    --inner join BIZ.DBT_STG.STG_ODOO_PROD__SALE_ORDER so On so.ID = sm.SALE_ID
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_SHIPMENT sh ON sh.id = sp.SHIPMENT_ID
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_PARTNER rp ON rp.ID = sm.PARTNER_ID
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__SHIPMENT_MODE shm ON shm.ID = sp.MODE_ID
    LEFT JOIN BIZ.DBT_ODOO.PRODUCTS Prod ON prod.PRODUCT_ID = sm.PRODUCT_ID
    LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_COUNTRY prc ON prc.id = rp.COUNTRY_ID
    LEFT JOIN NETSUM ON NETSUM.PRODUCT_ID = sm.PRODUCT_ID
  --- end of change --------------------  
WHERE
 so.SO_TYPE_ID = 2
  ORDER BY
 sp.CREATE_DATE DESC 
)

---- change to distinct -----
SELECT DISTINCT *
From Final