原材料表:
需求量:生产单里面mrp_production里面的需求数量,这里不能直接和产品相连,因为生产单里面是原材料而产品表里是成品,通过物料清单里的bom表与产品表相连
select
t6.产品ID,
t6.产品名称,
t6.材质,
t6.规格,
t6.实际库存,
(t6.需求总量-t6.已领总量+t6.退回总量) as 下单待领数,
(t6.实际库存-(t6.需求总量-t6.已领总量+t6.退回总量)) as 虚拟库存,
t6.在途料
FROM
(select t0.id as 产品ID ,t0.name_template as 产品名称,t1.material as 材质,t1.cust_spec as 规格,
COALESCE(t2.sjkc,0) as 实际库存 ,COALESCE(t3.ztqty,0) as 在途料,COALESCE(t8.xqqty ,0) as 需求总量,
COALESCE(t9.ylqty ,0) as 已领总量,COALESCE(t10.thqty ,0) as 退回总量
from product_product t0
left join product_template t1 on t0.product_tmpl_id=t1.id
--需求总量
LEFT JOIN ( select t5.product_id,sum(t0.materialkg) as xqqty from mrp_production t0
LEFT JOIN mrp_bom t1 on t1.id=t0.bom_id
LEFT JOIN mrp_bom_line t5 on t5.bom_id=t1.id
where t0.state!='done' and t0.company_id='4'
GROUP BY t0.product_id,t5.product_id
) t8 on t8.product_id=t0.id
-- 已领数量
LEFT JOIN(select t1.product_id , sum(t1.product_uom_qty) as ylqty from stock_picking t0
LEFT JOIN stock_move t1 on t0.id=t1.picking_id
where t1.location_id=get_warehouse_id('恒益原材料仓') and t1.location_dest_id=get_warehouse_id('恒益车间仓')
and t1.state='done'
GROUP BY t1.product_id ) t9 on t9.product_id=t0.id
--退回数量
LEFT JOIN(select t1.product_id , sum(t1.product_uom_qty) as thqty from stock_picking t0
LEFT JOIN stock_move t1 on t0.id=t1.picking_id
where t1.location_id=get_warehouse_id('恒益车间仓') and t1.location_dest_id=get_warehouse_id('恒益原材料仓')
and t1.state='done'
GROUP BY t1.product_id) t10 on t10.product_id=t0.id
--实际库存
left join(select product_id,sum(qty) sjkc from stock_quant where location_id=get_warehouse_id('恒益原材料仓') group by product_id) t2
on t2.product_id=t0.id
--在途料 在采购追踪表里面相连
left join( select product_id,sum(udlvqty) ztqty from purchase_order_track_report where udlvqty>0 and company='恒益' group by product_id)
t3 on t3.product_id=t0.name_template
where t1.categ_id=3 and t0.id<>15261 ) as t6
where t6.在途料!='0'
;