--净需求缺料报表
--在K/3 (10.3/10.4)上测试通过,感谢好望角!
select n.物料代码,n.物料名称,n.规格型号,n.仓库名称,n.库存数量,n.常用单位,n.总计划投料数,n.总已领数,n.总需求数,n.总待检仓库存数,
n.净外购在途数,n.净委外在途数,n.总需求数+n.净外购在途数+n.净委外在途数+n.总待检仓库存数 净需求数
from
(
select t2.物料代码,t2.物料名称,t2.规格型号,t1.仓库名称,t1.库存数量,t2.常用单位,t2.总计划投料数,t2.总已领数,
case when t5.待检仓库存数>0 then t5.待检仓库存数 else 0 end 总待检仓库存数,
case when t3.外购订料数>0 then t3.外购订料数 else 0 end - case when t3.外购入库数>0 then t3.外购入库数 else 0 end 净外购在途数,
case when t4.委外加工数>0 then t4.委外加工数 else 0 end - case when t4.委外入库数>0 then t4.委外入库数 else 0 end 净委外在途数,
case when t1.库存数量-t2.总计划投料数+t2.总已领数>0 then 0 when t1.库存数量-t2.总计划投料数+t2.总已领数<0 then t1.库存数量-t2.总计划投料数+t2.总已领数
when t1.库存数量-t2.总计划投料数+t2.总已领数 is null then 0-t2.总计划投料数+t2.总已领数 end 总需求数
from
(
select a.fitemid 物料内码,c.fnumber 物料代码,c.fname 物料名称,c.fmodel 规格型号,d.fname 常用单位,
sum(a.FAuxQtyMust) 总计划投料数,sum(a.FAuxStockQty) 总已领数
from ppbomentry a
inner join icmo b on a.ficmointerid=b.finterid
inner join t_icitem c on a.fitemid=c.fitemid
inner join t_measureunit d on c.fstoreunitid=d.fmeasureunitid
and b.fstatus in (1,5) and b.ftrantype = 85
where a.FAuxQtyMust <> a.FAuxStockQty and c.ferpclsid in (1,3) ----不含自制件
group by a.fitemid,c.fnumber,c.fname,c.fmodel,d.fname
) t2 --投料单
left join
(
select a.fitemid 物料内码,b.fnumber 物料代码,b.fname 物料名称,b.fmodel 规格型号,c.fname 仓库名称,
a.fqty/e.fcoefficient 库存数量,e.fname 常用单位
from icinventory a
inner join t_icitem b on a.fitemid=b.fitemid
inner join t_stock c on c.fitemid=a.fstockid
inner join t_UnitGroup d on b.funitgroupid=d.funitgroupid
inner join t_measureunit e on e.funitgroupid=b.funitgroupid and e.fmeasureunitid=b.fstoreunitid
where fqty<>0 --and fstockid in (401,406,413,10085,10147,10148)
) t1 on t1.物料内码 = t2.物料内码 --库存
left join
(
select c.fitemid 物料内码,c.fnumber 物料代码,c.fname 物料名称,c.fmodel 规格型号,
sum(a.fauxqty) 外购订料数,sum(a.fauxstockqty) 外购入库数
from poorderentry a inner join poorder b on a.finterid=b.finterid
inner join t_icitem c on a.fitemid=c.fitemid
where a.fauxqty<>a.fauxstockqty and b.fclosed<>1 and b.fcancellation=0 and b.fstatus<>0
group by c.fitemid,c.fnumber,c.fname,c.fmodel
) t3 on t3.物料内码=t2.物料内码 --采购订单
left join
--select * From icTransActionType
(
select b.fitemid 物料内码,b.fnumber 物料代码,b.fname 物料名称,b.fmodel 规格型号,
sum(a.fauxqty) 委外加工数,sum(a.fauxstockqty) 委外入库数
from icmo a inner join t_icitem b on a.fitemid=b.fitemid
where a.fauxqty<>a.fauxstockqty and a.ftrantype=571 and a.fstatus=1 --委外加工生产任务单
group by b.fitemid,b.fnumber,b.fname,b.fmodel
) t4 on t4.物料内码=t2.物料内码
left join
(
select a.fitemid 物料内码,b.fnumber 物料代码,b.fname 物料名称,b.fmodel 规格型号,c.fname 仓库名称,
a.fqty/e.fcoefficient 待检仓库存数,e.fname 常用单位
from POInventory a
inner join t_icitem b on a.fitemid=b.fitemid
inner join t_stock c on c.fitemid=a.fstockid
inner join t_UnitGroup d on b.funitgroupid=d.funitgroupid
inner join t_measureunit e on e.funitgroupid=b.funitgroupid and e.fmeasureunitid=b.fstoreunitid
where fqty<>0 and fstockid in (10161)
) t5 on t5.物料内码=t2.物料内码 --代管库存表
) n
where n.总需求数+n.净外购在途数+n.净委外在途数+n.总待检仓库存数<0
order by n.物料代码
