/*
 * PRODUCT
 */
/* Nur deutsche Übersetzung übernehmen */
UPDATE product_template a
SET name = b.value
FROM ir_translation b
WHERE a.id = b.res_id AND b.name = 'product.template,name' AND b.value <> '';

UPDATE product_template a
SET description_sale = b.value
FROM ir_translation b
WHERE a.id = b.res_id AND b.name = 'product.template,description_sale' AND b.value <> '';

UPDATE product_template a
SET description = b.value
FROM ir_translation b
WHERE a.id = b.res_id AND b.name = 'product.template,description' AND b.value <> '';

UPDATE product_template a
SET description_purchase = b.value
FROM ir_translation b
WHERE a.id = b.res_id AND b.name = 'product.template,description_purchase' AND b.value <> '';

/* product_taxes_rel -> product_template */
DELETE FROM product_taxes_rel WHERE prod_id IN (
	SELECT a.prod_id
	FROM product_taxes_rel a
	LEFT JOIN product_template b
	ON a.prod_id = b.id
	WHERE b.id IS NULL
);

/*
 * WORKFLOW
 */
/* Clean */
DELETE FROM wkf_instance WHERE res_type = 'purchase.order';
/* wkf_activity ("action_ship_create()") */
DELETE FROM wkf_activity WHERE id = 37;
/* wkf_workitem -> wkf_instance */
DELETE FROM wkf_workitem WHERE inst_id IN (
	SELECT w.inst_id 
	FROM wkf_instance i 
	RIGHT JOIN wkf_workitem w 
	ON i.id = w.inst_id 
	WHERE i.id IS NULL
);
/* wkf_workitem -> wkf_activity */
DELETE FROM wkf_workitem WHERE act_id IN (
	SELECT w.act_id 
	FROM wkf_activity a 
	RIGHT JOIN wkf_workitem w 
	ON a.id = w.act_id 
	WHERE a.id IS NULL
);
/* wkf_triggers -> wkf_instance */
DELETE FROM wkf_triggers WHERE instance_id IN (
	SELECT t.instance_id 
	FROM wkf_instance i 
	RIGHT JOIN wkf_triggers t 
	ON i.id = t.instance_id 
	WHERE i.id IS NULL
);
/* wkf_triggers -> wkf_workitem */
DELETE FROM wkf_triggers WHERE instance_id IN (
	SELECT t.instance_id 
	FROM wkf_workitem w 
	RIGHT JOIN wkf_triggers t 
	ON w.id = t.workitem_id 
	WHERE w.id IS NULL
);
/* wkf_transition -> wkf_activity */
DELETE FROM wkf_transition WHERE act_to IN (
	SELECT t.act_to
	FROM wkf_transition t
	LEFT JOIN wkf_activity a
	ON t.act_to = a.id
	WHERE a.id IS NULL
);
DELETE FROM wkf_transition WHERE act_from IN (
	SELECT t.act_from
	FROM wkf_transition t
	LEFT JOIN wkf_activity a
	ON t.act_from = a.id
	WHERE a.id IS NULL
);

/*
 * SALE_ORDER
 */

/* sale_order -> res_partner */
DELETE FROM sale_order WHERE partner_id IN (
	SELECT s.partner_id
	FROM sale_order s
	LEFT JOIN res_partner p
	ON s.partner_id = p.id
	WHERE p.id IS NULL
);
DELETE FROM sale_order WHERE partner_shipping_id IN (
	SELECT partner_shipping_id
	FROM sale_order s
	LEFT JOIN res_partner p
	ON s.partner_shipping_id = p.id
	WHERE p.id IS NULL
);
DELETE FROM sale_order WHERE partner_invoice_id IN (
	SELECT partner_invoice_id 
	FROM sale_order s
	LEFT JOIN res_partner p
	ON s.partner_invoice_id = p.id
	WHERE p.id IS NULL
);
/* sale_order_line -> product_product */
DELETE FROM sale_order_line WHERE product_id IN (
	SELECT l.product_id
	FROM sale_order_line l
	LEFT JOIN product_product p
	ON l.product_id = p.id
	WHERE p.id IS NULL
);
/* sale_order_line -> sale_order */
DELETE FROM sale_order_line WHERE order_id IN (
	SELECT l.order_id
	FROM sale_order_line l
	LEFT JOIN sale_order s
	ON l.order_id = s.id
	WHERE s.id IS NULL
);
/* sale_order_line -> procurement_order */
DELETE FROM sale_order_line WHERE procurement_id IN (
	SELECT l.procurement_id
	FROM sale_order_line l
	LEFT JOIN procurement_order o
	ON l.procurement_id = o.id
	WHERE o.id IS NULL
);
/* sale_advance_payment_inv -> product_product */
DELETE FROM sale_advance_payment_inv WHERE product_id IN (
	SELECT i.product_id
	FROM sale_advance_payment_inv i
	LEFT JOIN product_product p
	ON i.product_id = p.id
	WHERE p.id IS NULL
);
/* sale_order_line_invoice_rel -> sale_order_line */
DELETE FROM sale_order_line_invoice_rel WHERE order_line_id IN (
	SELECT a.order_line_id
	FROM sale_order_line_invoice_rel a
	LEFT JOIN sale_order_line b
	ON a.order_line_id = b.id
	WHERE b.id IS NULL
);
/* sale_order_line_invoice_rel -> account_invoice */
DELETE FROM sale_order_line_invoice_rel WHERE invoice_id IN (
	SELECT a.invoice_id
	FROM sale_order_line_invoice_rel a
	LEFT JOIN account_invoice_line b
	ON a.invoice_id = b.id
	WHERE b.id IS NULL
);
/* sale_order_invoice_rel -> sale_order */
DELETE FROM sale_order_invoice_rel WHERE order_id IN (
	SELECT a.order_id
	FROM sale_order_invoice_rel a
	LEFT JOIN sale_order b
	ON a.order_id = b.id
	WHERE b.id IS NULL
);
/* sale_order_invoice_rel -> account_invoice */
DELETE FROM sale_order_invoice_rel WHERE invoice_id IN (
	SELECT a.invoice_id
	FROM sale_order_invoice_rel a
	LEFT JOIN account_invoice b
	ON a.invoice_id = b.id
	WHERE b.id IS NULL
);

/*
 * STOCK
 */
 
/* Clean */
DELETE FROM stock_picking WHERE address_id = 1620;
/* stock_move -> product_product*/
DELETE FROM stock_move WHERE product_id IN (
	SELECT m.product_id 
	FROM stock_move m
	LEFT JOIN product_product p
	ON m.product_id = p.id
	WHERE p.id IS NULL
);
/* stock_move -> stock_location */
DELETE FROM stock_move WHERE location_dest_id IN (
	SELECT m.location_dest_id
	FROM stock_move m
	LEFT JOIN stock_location l
	ON m.location_dest_id = l.id
	WHERE l.id IS NULL
);
/* stock_move -> stock_picking */
DELETE FROM stock_move WHERE picking_id IN (
	SELECT m.picking_id
	FROM stock_move m
	LEFT JOIN stock_picking p
	ON m.picking_id = p.id
	WHERE p.id IS NULL
);
/* stock_inventory_line -> product_product */
DELETE FROM stock_inventory_line WHERE product_id IN (
	SELECT l.product_id
	FROM stock_inventory_line l
	LEFT JOIN product_product p
	ON l.product_id = p.id
	WHERE p.id IS NULL
);
/* stock_change_product_qty -> product_product */
DELETE FROM stock_change_product_qty WHERE product_id IN (
	SELECT s.product_id
	FROM stock_change_product_qty s
	LEFT JOIN product_product p
	ON s.product_id = p.id
	WHERE p.id IS NULL
); 
/* stock_partial_picking -> stock_picking */
DELETE FROM stock_partial_picking WHERE picking_id IN (
	SELECT pp.picking_id
	FROM stock_partial_picking pp
	LEFT JOIN stock_picking p
	ON pp.picking_id = p.id
	WHERE p.id IS NULL
);
/* stock_partial_picking_line -> stock_move */
DELETE FROM stock_partial_picking_line WHERE move_id IN (
	SELECT l.move_id
	FROM stock_partial_picking_line l
	LEFT JOIN stock_move m
	ON l.move_id = m.id
	WHERE m.id IS NULL
);
/* stock_partial_picking_line -> stock_partial_picking */
DELETE FROM stock_partial_picking_line WHERE wizard_id IN (
	SELECT l.wizard_id
	FROM stock_partial_picking_line l
	LEFT JOIN stock_partial_picking p
	ON l.wizard_id = p.id
	WHERE p.id IS NULL
);
/* procurement_order -> stock_move */
DELETE FROM procurement_order WHERE move_id IN (
	SELECT p.move_id
	FROM procurement_order p
	LEFT JOIN stock_move s
	ON p.move_id = s.id
	WHERE s.id IS NULL
);
/* stock_partial_move_line -> stock_move */
DELETE FROM stock_partial_move_line WHERE move_id IN (
	SELECT l.move_id
	FROM stock_partial_move_line l
	LEFT JOIN stock_move m
	ON l.move_id = m.id
	WHERE m.id IS NULL
);
/* stock_return_picking_memory -> stock_move */
DELETE FROM stock_return_picking_memory WHERE move_id IN (
	SELECT r.move_id
	FROM stock_return_picking_memory r
	LEFT JOIN stock_move m
	ON r.move_id = m.id
	WHERE m.id IS NULL
);
/* stock_move -> sale_order_line */
DELETE FROM stock_move WHERE sale_line_id IN (
	SELECT sale_line_id
	FROM stock_move m
	LEFT JOIN sale_order_line l
	ON m.sale_line_id = l.id
	WHERE l.id IS NULL
);
/* stock_picking -> sale_order */
DELETE FROM stock_picking WHERE sale_id IN (
	SELECT p.sale_id
	FROM stock_picking p
	LEFT JOIN sale_order o
	ON p.sale_id = o.id
	WHERE o.id IS NULL
);
/* stock_picking -> sale_order */
DELETE FROM stock_picking WHERE partner_id IN (
	SELECT a.partner_id
	FROM stock_picking a
	LEFT JOIN res_partner b
	ON a.partner_id = b.id
	WHERE b.id IS NULL
);

/*
 * ACCOUNT
 */
/* account_move_line -> product_product */
DELETE FROM account_move_line WHERE product_id IN ( 
	SELECT a.product_id
	FROM account_move_line a
	LEFT JOIN product_product b
	ON a.product_id = b.id
	WHERE b.id IS NULL
);
/* account_invoice_line -> product_product */
DELETE FROM account_invoice_line WHERE product_id IN ( 
	SELECT a.product_id
	FROM account_invoice_line a
	LEFT JOIN product_product b
	ON a.product_id = b.id
	WHERE b.id IS NULL
);

/*
 * OTHERS
 */

/* purchase_order_line -> product_product */
DELETE FROM purchase_order_line WHERE product_id IN (
	SELECT l.product_id
	FROM purchase_order_line l
	LEFT JOIN product_product p
	ON l.product_id = p.id
	WHERE p.id IS NULL
);
/* ir_actions_todo */
DELETE FROM ir_actions_todo WHERE action_id IS NULL;
ALTER TABLE ir_actions_todo ALTER COLUMN action_id SET NOT NULL;
/* ir_attachment */
DELETE FROM ir_attachment WHERE id IN (
	SELECT id
	FROM ir_attachment
	WHERE name = 'Etiketten.odt' OR name = 'Etiketten.pdf'
);
/* res_partner_address -> res_partner */
DELETE FROM res_partner_address WHERE partner_id IN (
	SELECT a.partner_id 
	FROM res_partner_address a 
	LEFT JOIN res_partner p 
	ON a.partner_id = p.id 
	WHERE p.id IS NULL
);
/* product_pricelist_version */
DELETE FROM product_pricelist_version WHERE id IN (2, 17, 18);
/* product_pricelist_item -> product_pricelist_version */
DELETE FROM product_pricelist_item WHERE price_version_id IN (
	SELECT i.price_version_id
	FROM product_pricelist_version v
	RIGHT JOIN product_pricelist_item i
	ON v.id = i.price_version_id
	WHERE v.id IS NULL
);