Untitled

select distinct
  (
    sm.id
  )
  , prod.default_code as part_number
  , prod.product_name as part_name
  , prod.PRODUCT_VALUE
  , loc.warehouse_name
  -- , sr.name as scrap_reason
  , sm.product_uom_qty as quarantine_qty
  , date_trunc('week', sm.date) as week_date
  , sm.state
  , sm.workorder_id
  , wo_prod.default_code as product_produced
  , wo_prod.product_name as product_produced_name
  , mo_prod.product_name as product_being_produced
from
  postgres_rds_odoo_public.stock_move sm

--   postgres_rds_odoo_public.stock_scrap as sc
--   left join postgres_rds_odoo_public.scrap_stock_reason_rel as ss on
--   sc.id = ss.scrap_stock_id
-- left join postgres_rds_odoo_public.scrap_reason as sr on
-- ss.reason_id = sr.id
left join dbt_odoo.locations as loc on
sm.location_id = loc.stock_location_id
left join dbt_odoo.products as prod on
sm.product_id = prod.product_id
left join postgres_rds_odoo_public.mrp_workorder as wo on
wo.id = sm.workorder_id
left join postgres_rds_odoo_public.mrp_production as mo on wo.production_id = mo.id
left join dbt_odoo.products as mo_prod on mo_prod.product_id = mo.product_id
left join dbt_odoo.products as wo_prod on
wo.product_id = wo_prod.product_id
where
workorder_id > 0 and sm.state = 'done' and year(sm.date) = 2022 and [product_produced=part_number] and [week_date=date] and sm.quarantine_production_id > 0
order by week_date asc, product_produced asc