金蝶K3生产投料单在制品数量重算SQL语句
本帖最后由 yueya1021 于 2019-6-24 15:07 编辑功能简介：修复投料单上的在制品数量字段信息主要是由于报废补料单，发生异常不能实现在制品扣减的情况可以修复。
使用方法在需要修复的数据库新建查询，粘贴以下代码，最后处输入生产投料单单号。
点执行前记得备份数据库。
===================================================================================================================================================
select distinct
--表头
t2.FSourceInterID ,t2.FSourceEntryID ,t2.FSourceBillNO,t2.FSourceTranType,t2.FPPBOMInterID v_FPPBOMInterID,t2.FPPBOMBillNO,t2.FPPBOMTrantype,t2.FICMOInterID v_FICMOInterID,t2.FICMOEntryID v_FICMOEntryID,t2.FICMOBillNO,
t2.v_FItemID,t2.v_FUnitID,t2.v_FBaseUnitID ,t2.FAuxQty,t2.FQty ,t2.FIsAuoGen,t2.FWIPDeductMode,t2.FCancellation,t2.FBillerID,t2.FDate,t2.FProductAuxPropID, t2.v_FAuxPropID ,
--表体
t2.FICMOInterID,t2.FICMOEntryID,t2.FPPBOMInterID,t2.FPPBOMEntryID,t2.FItemID,t2.FUnitID,t2.FBaseUnitID,t2.FStockID ,t2.FSPID ,t2.FMaterielType,t2.FBatchNO,
t2.FQtyConsume , t2.FAuxQtyConsume, t2.FAuxQtyMust, t2.FQtyMust, t2.FScrap, t2.FOperID, t2.FOperSN, t2.FPlanMode, t2.FMTONO, t2.FNote, t2.FAuxPropID
from(
--耗用记录生成方式（汇报单生成/产品入库单生成/委外加工入库单生成）
select distinct ve.FICMOInterID,ve.FPPBOMInterID,v.FSourceTranType--1063,1073,1108,1160,1161
from ICShop_ItemConsumeEntry ve
inner join ICShop_ItemConsume v on ve.FInterID =v.FInterID
)t1
inner join(--根据各种方式推算的耗用记录
selectv.FTranType FSourceTranType ,v.FInterIDFSourceInterID,v.FBillNo FSourceBillNO,v.FEntryIDFSourceEntryID,ppbom.FBillNo FPPBOMBillNO,PPBOM.FTranType FPPBOMTrantype,icmo.FBillNo FICMOBillNO,
v.FItemID v_FItemID,v.FUnitID v_FUnitID ,'' v_FBaseUnitID,v.FAuxQtyFinish FAuxQty,v.FQtyFinish FQty,1 FIsAuoGen,0 FWIPDeductMode ,0 FCancellation,16394 FBillerID,getdate() FDate,0 FProductAuxPropID,v.FAuxPropID v_FAuxPropID,
v.FICMOInterID,0 FICMOEntryID,PPBOM.FInterIDFPPBOMInterID,PPBOMEntry.FEntryID FPPBOMEntryID,
PPBOMEntry.FItemID FItemID,PPBOMEntry.FUnitIDFUnitID, 0 FBaseUnitID,PPBOMEntry.FStockIDFStockID ,PPBOMEntry.FSPID FSPID ,PPBOMEntry.FMaterielTypeFMaterielType,PPBOMEntry.FBatchNo FBatchNO,
--消耗数量=计划投料数量/计划生产数量*实作数量
PPBOMEntry.FAuxQtyMust/icmo.FAuxQty*v.FAuxQtyFinishFAuxQtyConsume,PPBOMEntry.FQtyMust/icmo.FQty*v.FQtyFinish FQtyConsume,
PPBOMEntry.FAuxQtyMust/icmo.FAuxQty*v.FAuxQtyMustFAuxQtyMust ,PPBOMEntry.FQtyMust/icmo.FQty*v.FQtyMustFQtyMust ,
PPBOMEntry.FScrapFScrap ,PPBOMEntry.FOperID FOperID ,
PPBOMEntry.FOperSNFOperSN ,PPBOMEntry.FPlanModeFPlanMode ,PPBOMEntry.FMTONo FMTONO,PPBOMEntry.FNote FNote,PPBOMEntry.FAuxPropID FAuxPropID
from (
--汇报时扣减：汇报单
select v.FTranType ,ve.FInterID ,v.FBillNo ,ve.FEntryID ,85 FICMOTranType,ve.FSourceInterId FICMOInterID,0 FICMOEntryID,
ve.FItemID ,ve.FUnitID, ve.FAuxPropID,
ve.FAuxQtyFinish ,ve.FQtyFinish ,ve.FAuxQtyFinish FAuxQtyMust ,ve.FQtyFinish FQtyMust
from ICMORptEntry ve
inner join ICMORpt v on ve.FInterID =v.FInterID
Where ve.FSourceTranType = 85 And v.FCancellation = 0
Union
--入库时扣减：产品入库单，委外加工入库单
select v.FTranType ,ve.finterid,v.FBillNo ,ve.FEntryID ,case when v.FTranType =5 then 1007105 else 85 end FICMOTranType,
case when v.FTranType =5 then ve.FOrderInterID else ve.FICMOInterID end FICMOInterID,
case when v.FTranType =5 then ve.FOrderEntryIDelse 0 end FICMOEntryID,ve.FItemID ,ve.FUnitID ,ve.FAuxPropID,
ve.FAuxQty , ve.FQty, ve.FAuxQtyMust, ve.FQtyMust
from ICStockBillEntry ve
inner join ICStockBill v on ve.FInterID =v.FInterID
where v.FTranType in(2,5) and (FICMOInterID>0 or FOrderInterID>0) and v.FCancellation =0
) v
inner join (
select FInterID,FBillNo,FTranType,0 FEntryID,FAuxQty,FQty
From icmo
where FClosed=0 and FStatus<>3
Union
select t1.FInterID,t1.FBillNo,t1.FClassTypeID FTranType,t2.FEntryID,t2.FAuxQty,t2.FQty
from icsubcontract t1
inner join ICSubContractEntryt2 on t1.FInterID =t2.FInterID
where t1.FClosed=0
)icmo on v.FICMOTranType =icmo.FTranType and v.FICMOEntryID =icmo.FEntryIDand v.FICMOInterID=icmo.FInterID
inner join PPBOMon v.FICMOInterID =PPBOM.FICMOInterID and v.FICMOEntryID =PPBOM.FOrderEntryID and PPBOM.FInterID in(1504)
inner join PPBOMEntryon PPBOM.FInterID =PPBOMEntry.FInterID
)t2 on t1.FSourceTranType =t2.FSourceTranType and t1.FPPBOMInterID =t2.FPPBOMInterID and t1.FICMOInterID =t2.FICMOInterID
left join(
select v.FSourceTranType ,v.FSourceInterID ,v.FSourceEntryID,
ve.FPPBOMInterID,ve.FPPBOMEntryID,
sum(ve.FQtyConsume) FQtyConsume , sum(ve.FAuxQtyConsume) FAuxQtyConsume, sum(ve.FAuxQtyMust) FAuxQtyMust, sum(ve.FQtyMust) FQtyMust
from ICShop_ItemConsumeentry ve
inner join ICShop_ItemConsume v on ve.FInterID=v.FInterID
group byv.FSourceTranType ,v.FSourceInterID ,v.FSourceEntryID,ve.FPPBOMInterID,ve.FPPBOMEntryID
)t3 on t2.FSourceTranType=t3.FSourceTranType and t2.FSourceInterID =t3.FSourceInterID and t2.FSourceEntryID =t3.FSourceEntryID and t2.FPPBOMInterID =t3.FPPBOMInterID and t2.FPPBOMEntryID=t3.FPPBOMEntryID
Where t3.FQtyConsume Is Null
order by t2.FSourceTranType,t2.FSourceInterID ,t2.FSourceEntryID ,t2.FPPBOMEntryID
update ve set ve.FAuxStockQty=case when ve.FMaterielType=376 then abs(isnull(vr.fauxqty,0)) else isnull(vr.fauxqty,0) end ,
ve.FStockQty=case when ve.FMaterielType=376 then abs(isnull(vr.fqty,0)) else isnull(vr.fqty,0) end ,
ve.FAuxQtySupply =isnull(vt.vfauxqtysupply,0) ,ve.FQtySupply =isnull(vt.vfqtysupply,0) ,
ve.FDiscardAuxQty =isnull(vt.vfauxqty,0) ,ve.FDiscardQty =isnull(vt.vfqty,0) ,
ve.FAuxQtyConsume =case when ve.FMaterielType=376 then 0 else ISNULL(vs.fauxqtyconsume,0) end,
ve.FQtyConsume =case when ve.FMaterielType=376 then 0 else ISNULL(vs.fqtyconsume,0) end,
ve.FWIPAuxQTY =case when ve.FMaterielType=376 then 0 else (ISNULL(vr.fauxqty,0)-ISNULL(vs.fauxqtyconsume,0)-ISNULL(vt.fauxqty ,0)) end,
ve.FWIPQTY =case when ve.FMaterielType=376 then 0 else (ISNULL(vr.fqty,0)-ISNULL(vs.fqtyconsume,0)-ISNULL(vt.fqty ,0)) end
from PPBOM v
inner join (
select FInterID,FBillNo,FTranType,0 FEntryID,FAuxQty,FQty ,FAuxQtyFinish ,FQtyFinish ,FAuxStockQty,FStockQty, FDiscardStockInAuxQty, FDiscardStockInQty
From ICMO
where FClosed=0 and FStatus<>3
Union
select t1.FInterID,t1.FBillNo,t1.FClassTypeID FTranType,t2.FEntryID,t2.FAuxQty,t2.FQty,0 FAuxQtyFinish ,0 FQtyFinish,t2.FAuxStockQty,t2.FStockQty,0 FDiscardStockInAuxQty,0 FDiscardStockInQty
from icsubcontract t1
inner join ICSubContractEntryt2 on t1.FInterID =t2.FInterID
where t1.FClosed=0
)ICMO on v.FICMOInterID =ICMO.FInterID and v.FSelTranType=ICMO.FTranType and v.FOrderEntryID=ICMO.FEntryID
inner join PPBOMEntry ve on v.FInterID =ve.FInterID
left join (--报废补料数量
select FICMOInterID,FOrderEntryID,FPPBOMEntryID,sum(case when t_SubMessage.FID ='Y' then FQty else 0 end) as fqty,
sum(case when t_SubMessage.FID ='Y' then FAuxQty else 0 end) as fauxqty,
sum(case when t_SubMessage.FID ='Y' then FQtySupply else 0 end) as fqtysupply,
sum(case when t_SubMessage.FID ='Y' then FAuxQtySupply else 0 end) as fauxqtysupply,
sum(FQty) as VFQty,sum(FAuxQty) as VFAuxQty,sum(FQtySupply) as VFQtySupply,sum(FAuxQtySupply) as VFAuxQtySupply
From ICItemScrapEntry
inner join ICItemScrap on ICItemScrap.FInterID =ICItemScrapEntry.FInterID
inner join t_SubMessage on ICItemScrapEntry.FWIPReduce =t_SubMessage.FInterID
where ICItemScrap.fcancellation=0
group by FICMOInterID,FOrderEntryID,FPPBOMEntryID
)vt on vt.FICMOInterID = ve.FICMOInterID and vt.FOrderEntryID=v.FOrderEntryID and vt.FPPBOMEntryID =ve.FEntryID
left join(--消耗数量
select ICShop_ItemConsumeEntry.FPPBOMInterID,ICShop_ItemConsumeEntry.FPPBOMEntryID,sum(ICShop_ItemConsumeEntry.FQtyConsume) as fqtyconsume,sum(ICShop_ItemConsumeEntry.FQtyMust) as fqtymust ,
sum(ICShop_ItemConsumeEntry.FAuxQtyConsume) as fauxqtyconsume,sum(ICShop_ItemConsumeEntry.FAuxQtyMust) as fauxqtymust
From ICShop_ItemConsumeEntry
inner join ICShop_ItemConsume on ICShop_ItemConsumeEntry.FInterID=ICShop_ItemConsume.FInterID
where ICShop_ItemConsume.FCancellation=0
group by ICShop_ItemConsumeEntry.FPPBOMInterID,ICShop_ItemConsumeEntry.FPPBOMEntryID
)vs on vs.FPPBOMInterID =ve.FInterIDand vs.FPPBOMEntryID=ve.FEntryID
left join(--已领数量
select ICStockBillEntry .FICMOInterID,ICStockBillEntry .FPPBomEntryID ,case when ICStockBill.FTranType=24 or icstockbill.FTranType=1024 then 85 else 1007105 end FSelTranType,
SUM(ICStockBillEntry .FQty ) as fqty,SUM(ICStockBillEntry .FAuxQty) as fauxqty,
SUM(ICStockBillEntry .FQtyMust) as fqtymust,SUM(ICStockBillEntry .FAuxQtyMust) as fauxqtymust
From ICStockBillEntry
inner join ICStockBill on ICStockBillEntry .FInterID =ICStockBill.FInterID
where ICStockBill.FCancellation=0 and ICStockBill.FTranType in(24,1024,28,1028)
group by ICStockBill.FTranType,FICMOInterID ,ICStockBillEntry.FPPBomEntryID
Union
select ICSTJGBillEntry .FICMOInterID,ICSTJGBillEntry .FPPBomEntryID ,85FSelTranType,
SUM(ICSTJGBillEntry .FQty ) as fqty,SUM(ICSTJGBillEntry .FAuxQty) as fauxqty,
SUM(ICSTJGBillEntry .FQtyMust) as fqtymust,SUM(ICSTJGBillEntry .FAuxQtyMust) as fauxqtymust
From ICSTJGBillEntry
inner join ICSTJGBill on ICSTJGBillEntry .FInterID =ICSTJGBill.FInterID
Where ICSTJGBill.FCancellation = 0 And ICSTJGBill.FTranType = 137
group by ICSTJGBill.FTranType,FICMOInterID ,ICSTJGBillEntry.FPPBomEntryID
)vr on vr.FSelTranType=v.FSelTranType andvr.FICMOInterID=(case when v.FSelTranType=1007105 then ve.finterid else ve.FICMOInterID end)and vr.FPPBomEntryID =ve.FEntryID
Where v.FBillNo='生产投料单单号'
===========================================================================
======================================================================================================================================================
页:
[1]