工作记录（8）

 SELECT *, (current_qty-(case when occupied_qty is null then 0 else occupied_qty end)) overdosage_qty from (
            SELECT log.id as id, log.location_id, log.product_id, p.fullcode, p.categ_grade_one, p.categ_grade_two, p.categ_grade_three, log.balance_qty current_qty, p.product_uom as product_uom_id, 
(select sum((lot.product_qty-lot.welded_qty)*bom_line.product_qty) from stock_production_lot lot join  product_bom bom on lot.product_id = bom.product_id and lot_type = 'outsourcing_lot' and lot.state = 'plan' join product_bom_line bom_line on bom.id = bom_line.bom_id where bom_line.product_id=p.id and lot.src_location_id=log.location_id) as occupied_qty
  from product_product p join stock_product_cost_log log on p.id = log.product_id and log.end_time >= '4000-12-31' 
  where log.location_id in (select id from stock_location where usage='internal' and location_type='outsourcing_workshop')) t