做过实际成本的同仁都知道，在实际成本计算中，最难取得的数据是投产量。
有人会通过月末假退料的模式，使本期投产量=本期入库量，但工作量先不说，数据可能更乱。
如生产任务单是1000套，本月领料800套，本月入库600套，那在制品应该是多少呢？
可能有人会说是400套，但正确的答案是200套，答案计算起来似乎很简单800-600=200
然而事实并没有这么简单，800从哪里来？生产部填写？
更让人头大的是，领料会刚好按套数领吗？
在此背景下，结合会计中的约当系数法，故提出约当投产量法的概念，给各位小伙伴分享一下：
计算公式：任务单本期约当投入产量=（领料数量*单价-以前期间领料数量*单价/（投料单数量*单价）
注：一旦任务单结案，则任务单投入产量=入库产量（没考虑废品入库）
领料是否配套，都能计算出一个投产量，如果完全配套，属于理想状态，与实际一致。
该方法适合场景：生产周期不能太长或价格不能剧烈波动，满足其一即可，否则期间较长且价格剧烈波动，可能会导致最后期间的投产量为负数。
代码是4年前写的，分享给大家，比较粗糙，见笑了：
----------------------按产品对象汇总-----------------------
SET NOCOUNTON
create TABLE#furtrue_cost(
[FinterID] [int] identity(1,1) primary key,--主键
[Fdate] [datetime] NOT NULL,--成本计算月末日期
[FPPBom] [int] NOT NULL,--任务单内码
[Fitemid] [int] NOT NULL,--产品内码
[FBOMid] [int] NOT NULL,--BOM内码
[FPlanqty] [FLOAT] ,--计划生产数量
[Ffurtureqty] [FLOAT] ,--未领料约当投产量
[Fallqty] [FLOAT] ,--累计约当投产量
[Fallqtyreal] [FLOAT] ,--累计实际入库量
[Fperiodqtyreal] [FLOAT] ,--未来期间实际入库量
[Flastdayqty] [FLOAT] --期末在制品数量
)
insert into#furtrue_cost--插入已结案且在本期及以后期间有产品入库的生产任务单
select '########',finterid,fitemid,FBomInterID,fqty,0,0,0,0,0 from icmo
where FTranType=85 and (FStatus=3 or FSuspend=1) and finterid in
( selectdistinct FICMOInterID from ICStockBill t1 inner join ICStockBillEntryt2 on t1.FInterID=t2.FInterID
and FTranType=2 and t1.fdate>='********'
)
insert into#furtrue_cost--插入未结案本期及以前期间有生产领料的生产任务单
select '########',finterid,fitemid,FBomInterID,fqty,0,0,0,0,0 from icmo
where FTranType=85 and FStatus<>3 and FSuspend=0 and finterid in
( selectdistinct FICMOInterID from ICStockBill t1 inner join ICStockBillEntryt2 on t1.FInterID=t2.FInterID
and FTranType=24 and t1.fdate<='########'
)
update #furtrue_costset Fallqtyreal=t3.Fallqtyreal--更新累计产品入库数量
from
(select FICMOInterID,FItemID,SUM(FQty) as Fallqtyreal from ICStockBill t1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID and FICMOInterID in
(select fPPBOMfrom #furtrue_cost)
and FTranType=2 and FDate<='########'
group by FICMOInterID,FItemID) as t3 where FPPBom=t3.FICMOInterID
and #furtrue_cost.Fitemid=t3.FItemID
and fdate='########'
update #furtrue_costset Fperiodqtyreal=t3.Fallqtyreal--更新本期产品入库数量
from
(select FICMOInterID,FItemID,SUM(FQty) as Fallqtyreal from ICStockBill t1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID and FICMOInterID in
(select fPPBOMfrom #furtrue_cost)
and FTranType=2 and FDate>'########'
group by FICMOInterID,FItemID) as t3 where FPPBom=t3.FICMOInterID
and #furtrue_cost.Fitemid=t3.FItemID
and fdate='########'
delete from#furtrue_cost
where FPlanqty=Fallqtyreal and FPPBom not in
(select FICMOInterIDfrom ICStockBillt1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID
and FTranType=2 and FDate<='########' and FDate>='********'
)
CREATE TABLE#mtfurture_cost
(
[FinterID] [int] identity(1,1) primary key,--主键
[Fdate] [datetime] NOT NULL,--成本计算月末日期
[FPPBom] [int] NOT NULL,--任务单内码
[Fcpitemid] [int] NOT NULL,--产品内码
[Fclitemid] [int] NOT NULL,--材料内码
[Fstandardqty] [FLOAT] ,--标准用量
[Fplanqty] [FLOAT] ,--计划投料数量
[Flastclqty] [FLOAT] ,--累计领料数量
[Fleftqty] [FLOAT] ,--未领料数量
[Fprice] [FLOAT] --最新单价
)
insert into #mtfurture_cost--插入本期有领用材料的任务单内码、产品内码，投料单物料内码
select distinct'########',
t5.FICMOInterID,t5.FItemID,t6.FItemID,FQtyScrap*(1+FScrap), t6.FQtyMust,0,0,0 from PPBOM t5 inner join PPBOMEntry t6 on t5.FInterID=t6.FInterID
and t5.FICMOInterIDin (selectdistinct FPPBOM from #furtrue_cost where FDate='########')
update #mtfurture_costset Flastclqty=t3.Flastclqty,Fleftqty=Fplanqty-isnull(t3.Flastclqty,0)--截至到本期末累计领用材料数量
from
(select FICMOInterID,FItemID,SUM(FQty) as Flastclqty from ICStockBill t1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID
and FTranType=24 and t2.FICMOInterIDin
(select distinctFPPBOM from #furtrue_cost where FDate='########')
and t1.FDate<='########'
group by FICMOInterID,FItemID) as t3 where FPPBom=t3.FICMOInterID
and Fclitemid=t3.FItemID and Fdate='########'
update #mtfurture_costset Fleftqty=case when Fplanqty-Flastclqty<0 then 0 else Fplanqty-Flastclqtyend
where Fdate='########'
update #mtfurture_costset Fprice=t1.F_104--从物料档案中取得材料成本
from t_ICItemt1
where t1.FItemID=#mtfurture_cost.Fclitemid
and Fdate='########'
update #mtfurture_costset Fprice=t21.sumclamount--更新半成本成本
from
(select t1.FPPBom,t1.Fcpitemid,sum(t1.Fstandardqty*t1.Fprice) as sumclamount
from #mtfurture_cost t1where t1.Fdate='########'
group by t1.FPPBom,t1.Fcpitemid
) ast21
where t21.Fcpitemid=#mtfurture_cost.Fclitemid
and Fdate='########' and t21.sumclamount>0
update #mtfurture_cost setFprice=t21.sumclamount--再次更新半成品成本
from
(select t1.FPPBom,t1.Fcpitemid,sum(t1.Fstandardqty*t1.Fprice) as sumclamount
from #mtfurture_cost t1where t1.Fdate='########'
group by t1.FPPBom,t1.Fcpitemid
) ast21
where t21.Fcpitemid=#mtfurture_cost.Fclitemid
and Fdate='########' and t21.sumclamount>0
update #furtrue_costset Ffurtureqty=t21.qty--计算未领料的约当套量
from
(select t1.FPPBom,t1.Fcpitemid,(case when sum(t1.Fstandardqty*t1.Fprice) =0 then 0 else
sum(t1.fleftqty*t1.Fprice)/SUM(t1.Fstandardqty*t1.Fprice) end) as qty
from #mtfurture_cost t1where t1.Fdate='########'
group by t1.FPPBom,t1.Fcpitemid
) ast21
where t21.FPPBom=#furtrue_cost.FPPBomand t21.Fcpitemid=#furtrue_cost.Fitemidand Fdate='########'
update #furtrue_costset Fallqty=case when FPlanqty-Ffurtureqty<=0 then 0 elseFPlanqty-Ffurtureqty end --计算累计约当领料套量
update #furtrue_costset Flastdayqty=case when Fallqty-Fallqtyreal<=0 then 0 elseFallqty-Fallqtyreal end--计算期末在制品数量
update #furtrue_costset Flastdayqty=0 where FPlanqty<=Fallqtyreal--如果计划生产数量小于等于实际入库量，则在制品数量更新为
update #furtrue_costset Flastdayqty=Fperiodqtyreal from
ICMO t1inner join #furtrue_costt2 on t1.FInterID =t2.FPPBom and t1.FItemID=t2.Fitemid and t1.FStatus=3
and t2.FPPBomnot in (selectFICMOInterID from ICStockBill t1 inner join ICStockBillEntry t2
on t1.FInterID=t2.FInterID and t1.FTranType=24 and t1.FDate>'########')
select t2.FNumberas 成本对象代码,t2.FName as成品对象名称,t2.FModel as产品规格型号, Round(SUM(t1.Flastdayqty),0) as 期末在制品数量
from #furtrue_costt1 inner join t_ICItem t2 on t1.Fitemid=t2.FItemID andt1.Fdate='########'
group by t2.FItemID,t2.FNumber ,t2.FName ,t2.FModel
drop table#furtrue_cost
drop table#mtfurture_cost
-------------------------------按任务单汇总----------------------------------------------
SET NOCOUNTON
create TABLE#furtrue_cost(
[FinterID] [int] identity(1,1) primary key,--主键
[Fdate] [datetime] NOT NULL,--成本计算月末日期
[FPPBom] [int] NOT NULL,--任务单内码
[Fitemid] [int] NOT NULL,--产品内码
[FBOMid] [int] NOT NULL,--BOM内码
[FPlanqty] [FLOAT] ,--计划生产数量
[Ffurtureqty] [FLOAT] ,--未领料约当投产量
[Fallqty] [FLOAT] ,--累计约当投产量
[Fallqtyreal] [FLOAT] ,--累计实际入库量
[Fperiodqtyreal] [FLOAT] ,--未来期间实际入库量
[Flastdayqty] [FLOAT] --期末在制品数量
)
insert into#furtrue_cost--插入已结案且在本期及以后期间有产品入库的生产任务单
select '########',finterid,fitemid,FBomInterID,fqty,0,0,0,0,0 from icmo
where FTranType=85 and (FStatus=3 or FSuspend=1) and finterid in
( selectdistinct FICMOInterID from ICStockBill t1 inner join ICStockBillEntryt2 on t1.FInterID=t2.FInterID
and FTranType=2 and t1.fdate>='********'
)
insert into#furtrue_cost--插入未结案本期及以前期间有生产领料的生产任务单
select '########',finterid,fitemid,FBomInterID,fqty,0,0,0,0,0 from icmo
where FTranType=85 and FStatus<>3 and FSuspend=0 and finterid in
( selectdistinct FICMOInterID from ICStockBill t1 inner join ICStockBillEntryt2 on t1.FInterID=t2.FInterID
and FTranType=24 and t1.fdate<='########'
)
update #furtrue_costset Fallqtyreal=t3.Fallqtyreal--更新累计产品入库数量
from
(select FICMOInterID,FItemID,SUM(FQty) as Fallqtyreal from ICStockBill t1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID and FICMOInterID in
(select fPPBOMfrom #furtrue_cost)
and FTranType=2 and FDate<='########'
group by FICMOInterID,FItemID) as t3 where FPPBom=t3.FICMOInterID
and #furtrue_cost.Fitemid=t3.FItemID
and fdate='########'
update #furtrue_costset Fperiodqtyreal=t3.Fallqtyreal--更新本期产品入库数量
from
(select FICMOInterID,FItemID,SUM(FQty) as Fallqtyreal from ICStockBill t1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID and FICMOInterID in
(select fPPBOMfrom #furtrue_cost)
and FTranType=2 and FDate>'########'
group by FICMOInterID,FItemID) as t3 where FPPBom=t3.FICMOInterID
and #furtrue_cost.Fitemid=t3.FItemID
and fdate='########'
delete from#furtrue_cost
where FPlanqty=Fallqtyreal and FPPBom not in
(select FICMOInterIDfrom ICStockBillt1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID
and FTranType=2 and FDate<='########' and FDate>='********'
)
CREATE TABLE#mtfurture_cost
(
[FinterID] [int] identity(1,1) primary key,--主键
[Fdate] [datetime] NOT NULL,--成本计算月末日期
[FPPBom] [int] NOT NULL,--任务单内码
[Fcpitemid] [int] NOT NULL,--产品内码
[Fclitemid] [int] NOT NULL,--材料内码
[Fstandardqty] [FLOAT] ,--标准用量
[Fplanqty] [FLOAT] ,--计划投料数量
[Flastclqty] [FLOAT] ,--累计领料数量
[Fleftqty] [FLOAT] ,--未领料数量
[Fprice] [FLOAT] --最新单价
)
insert into #mtfurture_cost--插入本期有领用材料的任务单内码、产品内码，投料单物料内码
select distinct'########',
t5.FICMOInterID,t5.FItemID,t6.FItemID,FQtyScrap*(1+FScrap), t6.FQtyMust,0,0,0 from PPBOM t5 inner join PPBOMEntry t6 on t5.FInterID=t6.FInterID
and t5.FICMOInterIDin (selectdistinct FPPBOM from #furtrue_cost where FDate='########')
update #mtfurture_costset Flastclqty=t3.Flastclqty,Fleftqty=Fplanqty-isnull(t3.Flastclqty,0)--截至到本期末累计领用材料数量
from
(select FICMOInterID,FItemID,SUM(FQty) as Flastclqty from ICStockBill t1
inner joinICStockBillEntry t2
on t1.FInterID=t2.FInterID
and FTranType=24 and t2.FICMOInterIDin
(select distinctFPPBOM from #furtrue_cost where FDate='########')
and t1.FDate<='########'
group by FICMOInterID,FItemID) as t3 where FPPBom=t3.FICMOInterID
and Fclitemid=t3.FItemID and Fdate='########'
update #mtfurture_costset Fleftqty=case when Fplanqty-Flastclqty<0 then 0 else Fplanqty-Flastclqtyend
where Fdate='########'
update #mtfurture_costset Fprice=t1.F_104--从物料档案中取得材料成本
from t_ICItemt1
where t1.FItemID=#mtfurture_cost.Fclitemid
and Fdate='########'
update #mtfurture_costset Fprice=t21.sumclamount--更新半成本成本
from
(select t1.FPPBom,t1.Fcpitemid,sum(t1.Fstandardqty*t1.Fprice) as sumclamount
from #mtfurture_cost t1where t1.Fdate='########'
group by t1.FPPBom,t1.Fcpitemid
) ast21
where t21.Fcpitemid=#mtfurture_cost.Fclitemid
and Fdate='########' and t21.sumclamount>0
update #mtfurture_cost setFprice=t21.sumclamount--再次更新半成品成本
from
(select t1.FPPBom,t1.Fcpitemid,sum(t1.Fstandardqty*t1.Fprice) as sumclamount
from #mtfurture_cost t1where t1.Fdate='########'
group by t1.FPPBom,t1.Fcpitemid
) ast21
where t21.Fcpitemid=#mtfurture_cost.Fclitemid
and Fdate='########' and t21.sumclamount>0
update #furtrue_costset Ffurtureqty=t21.qty--计算未领料的约当套量
from
(select t1.FPPBom,t1.Fcpitemid,(case when sum(t1.Fstandardqty*t1.Fprice) =0 then 0 else
sum(t1.fleftqty*t1.Fprice)/SUM(t1.Fstandardqty*t1.Fprice) end) as qty
from #mtfurture_cost t1where t1.Fdate='########'
group by t1.FPPBom,t1.Fcpitemid
) ast21
where t21.FPPBom=#furtrue_cost.FPPBomand t21.Fcpitemid=#furtrue_cost.Fitemidand Fdate='########'
update #furtrue_costset Fallqty=case when FPlanqty-Ffurtureqty<=0 then 0 elseFPlanqty-Ffurtureqty end --计算累计约当领料套量
update #furtrue_costset Flastdayqty=case when Fallqty-Fallqtyreal<=0 then 0 elseFallqty-Fallqtyreal end--计算期末在制品数量
update #furtrue_costset Flastdayqty=0 where FPlanqty<=Fallqtyreal--如果计划生产数量小于等于实际入库量，则在制品数量更新为0
update #furtrue_costset Flastdayqty=Fperiodqtyreal from
ICMO t1inner join #furtrue_costt2 on t1.FInterID =t2.FPPBom and t1.FItemID=t2.Fitemid and t1.FStatus=3
and t2.FPPBomnot in (selectFICMOInterID from ICStockBill t1 inner join ICStockBillEntry t2
on t1.FInterID=t2.FInterID and t1.FTranType=24 and t1.FDate>'########')
select t3.fbillnoas 生产任务单编号,t2.FNumber as成本对象代码,t2.FName as成品对象名称,t2.FModel as产品规格型号, ROUND(SUM(t1.Flastdayqty),0) as 期末在制品数量
from #furtrue_costt1 inner join t_ICItem t2 on t1.Fitemid=t2.FItemID andt1.Fdate='########'
INNER JOINICMO t3 ON t1.fppbom=t3.finterid AND t1.Flastdayqty<>0
group by t3.fbillno,t2.FItemID,t2.FNumber ,t2.FName ,t2.FModel
drop table#furtrue_cost
drop table#mtfurture_cost
推荐阅读