Untitled

SELECT
  sil.id as sil_id
  , sml.id as sml_id
  , sil.THEORETICAL_QTY
  , sil.SECONDARY_COUNT
  , sil.PRODUCT_QTY
  , pt.DEFAULT_CODE
  , sl.COMPLETE_NAME as location
  , sml.WRITE_DATE as complete_date
  , scc.NAME as cycle_count
  , sl.DISCREPANCY_THRESHOLD as disc_threshold
FROM
-- gimme the inventory adjustments
  BIZ.DBT_STG.STG_ODOO_PROD__STOCK_INVENTORY_LINE sil 
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_INVENTORY si on si.id = sil.INVENTORY_ID
-- now join the moves so i can set qty = 0 constraints
  LEFT join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE sm on sm.INVENTORY_ID = si.ID and sm.PRODUCT_ID = sil.PRODUCT_ID
  LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE_LINE sml on sml.MOVE_ID = sm.ID and sml.LOT_ID = sil.PROD_LOT_ID
-- get cycle count information
  left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_CYCLE_COUNT scc on scc.id = si.CYCLE_COUNT_ID
  left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_SLOT_VERIFICATION_REQUEST svr on svr.CYCLE_COUNT_ID = scc.ID and svr.PRODUCT_ID = sil.PRODUCT_ID
-- get misc info
  join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_LOCATION sl on sl.id = sml.LOCATION_ID
  join BIZ.DBT_STG.STG_ODOO_PROD__PRODUCT_PRODUCT pp on sil.PRODUCT_ID = pp.id
  join BIZ.DBT_STG.STG_ODOO_PROD__PRODUCT_TEMPLATE pt on pt.id = pp.PRODUCT_TMPL_ID
WHERE
  sml.QTY_DONE = 0
   --and sil.id = 8738872
  --and sml.state = 'done'
ORDER BY
  sml.WRITE_DATE DESC