Data Validation

CREATE MATERIALIZED VIEW AS middleware_table
SELECT product, date, store, sum(qty) as qty FROM middleware_table
WHERE create_date = (hari ini)
GROUP BY product, date, store

CREATE MATERIALIZED VIEW AS odoo_stock_move_table
SELECT product_id, date, store_code, sum(qty_done) as qty FROM stock_move_line
WHERE create_date = (hari ini)
GROUp BY product_id, date, store_code

CREATE MATERIALIZED VIEW AS odoo_stock_move_line_table
SELECT product_id, date, store_code, sum(qty_done) as qty FROM stock_move
WHERE create_date = (hari ini)
GROUp BY product_id, date, store_code

CREATE MATERIALIZED VIEW AS xxx_data_validation_stock_move_line_result
SELECT middle.product, middle.date, middle.store, middle.qty, odoo.product_id, odoo.date, odoo.store_code, odoo.qty, 
case when middle.qty != odoo.qty then true else false end as is_diff
FROM middleware_table middle 
FULL OUTER JOIN odoo_stock_move_table ON store_code = store AND product = product_id AND middle.date = odoo.date

CREATE MATERIALIZED VIEW AS xxx_data_validation_stock_move_result
SELECT middle.product, middle.date, middle.store, middle.qty, odoo.product_id, odoo.date, odoo.store_code, odoo.qty, 
case when middle.qty != odoo.qty then true else false end as is_diff
FROM middleware_table middle 
FULL OUTER JOIN odoo_stock_move_line_table ON store_code = store AND product = product_id AND middle.date = odoo.date