SELECT
sil.id
, sil.THEORETICAL_QTY
, sil.PRODUCT_QTY
, pt.DEFAULT_CODE
, sl.COMPLETE_NAME as location
, sml.WRITE_DATE as complete_date
, scc.NAME as cycle_count
, scc.TOTAL_COST_MOVES_NET as net_cost
, 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
LEFT JOIN BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE_LINE sml on sml.MOVE_ID = sm.ID
-- get cycle count information
left join BIZ.DBT_STG.STG_ODOO_PROD__STOCK_CYCLE_COUNT scc on scc.id = si.CYCLE_COUNT_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 sml.state = 'done'
ORDER BY
sml.WRITE_DATE DESC