find mismatches
user_0223141589
mysql
2 years ago
1.2 kB
4
Indexable
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'
Editor is loading...