find mismatches

 avatar
user_0223141589
mysql
a year ago
1.2 kB
0
Indexable
Never
WITH max_bom AS (
  SELECT
    mb.TYPE,
    mb.PRODUCT_TMPL_ID,
    mb.code
  FROM
    BIZ.DBT_STG.STG_ODOO_PROD__MRP_BOM mb
    JOIN BIZ.DBT_STG.STG_ODOO_PROD__PRODUCT_TEMPLATE pt ON pt.id = mb.PRODUCT_TMPL_ID
    JOIN (
      SELECT
        MAX(mb.create_date) AS date,
        mb.PRODUCT_TMPL_ID,
        mb.id
      FROM
        BIZ.DBT_STG.STG_ODOO_PROD__MRP_BOM mb
      GROUP BY
        2,
        3
    ) a ON a.id = mb.id
    AND a.date = mb.CREATE_DATE
  --WHERE
  --  pt.DEFAULT_CODE = '06950-001'
)
SELECT
  sm.id,
  sm.PRODUCT_ID AS sm_prod,
  pol.PRODUCT_ID AS pol_prod,
  sm.PRODUCT_QTY,
  sm.STATE as sm_state,
  mb.TYPE as bom_type
FROM
  BIZ.DBT_STG.STG_ODOO_PROD__STOCK_MOVE sm
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__PURCHASE_ORDER_LINE pol ON pol.id = sm.PURCHASE_LINE_ID
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__PURCHASE_ORDER po ON po.id = pol.ORDER_ID
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__PRODUCT_PRODUCT pp ON pp.id = pol.PRODUCT_ID
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__PRODUCT_TEMPLATE pt ON pt.id = pp.PRODUCT_TMPL_ID
  LEFT JOIN max_bom mb ON mb.PRODUCT_TMPL_ID = pt.ID
WHERE
sm.PRODUCT_ID != pol.PRODUCT_ID
AND sm.state NOT IN ('cancel')
AND mb.type = 'normal'