⽤SQL查询分析实现类似⾦蝶K3的收发存明细表使⽤SQL查询分析实现类收发存的报表，原始需求在的第四部分。下图是实现需求。
⼀、准备
删除临时表
[buy]判断是否存在临时表，存在则删除[/buy]
if OBJECT_ID('tempdb..#inv') is not null
drop table #inv
if OBJECT_ID('tempdb..#t_mto') is not null
drop table #t_MTO
if OBJECT_ID('tempdb..#t_mtoentry') is not null
drop table #t_MTOEntry
判断是否存在需调整记录
[buy]判断采购申请单上是否存在需要调整的记录，存在则继续。
需要满⾜条件是：
1.采购申请单上存在MTO计划模式的物料，计划跟踪号不为999999；
2.库存中有可调整的物料。
那么库存中哪些是可以调整的物料呢？还得满⾜以下条件：
2.1批号为999999；
2.2计划跟踪号为空或者999999；
3.3⽆浮动计量单位的，基本库存数量要⼤于0，有浮动计量单位的，辅助数量和基本数量的库存均需⼤于0；
[/buy]
declare @finterid int = 1059
if exists (lect f1 from (
lect ca when (ti.FSecUnitID = 0 and t2.FQty > 0) OR (ti.FSecUnitID > 0 and t2.FQty > 0 and t2.FSecQty > 0 ) then 1 el 0 end as "F1"
from PORequestEntry t1
left join ICInventory t2 on t1.FItemID = t2.FItemID
left join t_ICItem ti on t1.FItemID = ti.FItemID
where t1.FInterID = @finterid
and t1.FPlanMode = '14035'
and t1.FMTONo <> '999999'
and t2.FBatchNo = '999999'
and t2.FMTONo in ('','999999')
) as t where f1 = 1
)
⼆、开始
begin
寻找内码
查询mto调整单的内码
declare @maxNum int
lect @maxNum = FMaxNum from ICMaxNum where FTableName = 't_MTOChange'
t @maxNum = @maxNum + 1
心理健康月
update ICMaxNum t FMaxNum = @maxNum
构建表结构
将采购申请、存货表、需计算的字段进⾏联结，并构成临时表#inv
lect
t3.FBillNo,
t1.FEntryID,
t1.FDetailID,
t1.FItemID,
t1.FMTONo as "t1FMTONo",
ti.FSecUnitID,
t1.FQty as "t1FQty",
t1.FCommitQty,
t1.FUnitID,
tm.FCoefficient,
t1.FAuxCommitQty,
t1.FAuxQty,
t1.FSecQty as "t1FSecQty",
t1.FSecCommitQty,
t1.FOrderQty,
t1.FMRPClod,
t1.FPlanMode,
t1.FEntrySelfP0139, --辅助数量（计算）
t1.FEntrySelfP0140, --关联数量
t1.FEntrySelfP0141, --关联标志
t2.FStockID,
t2.FStockPlaceID,
t2.FKFPeriod,
t2.FKFDate,
老北京炸酱面的做法及配料t2.FQty as "t2FQty",
t2.FSecQty as "t2FSecQty",
ca when t2.FSecQty > 0 then t2.FQty / t2.FSecQty el 0 end as "FSecCoefficient",
t2.FBatchNo,
t2.FMTONo as "t2FMTONo",
CAST(null as int) as "FMTOInterID",
CAST(null as decimal(28,10)) as "FChaQty",
cast(null as decimal(28,10)) as "FMTOChange",
cast(null as decimal(28,10)) as "FSumMTOChange",
CAST(null as decimal(28,10)) as "FBegQty",
CAST(null as decimal(28,10)) as "FEndQty",
cast(null as decimal(28,10)) as "FSecChaQty",
cast(null as decimal(28,10)) as "FSecMTOChange",
cast(null as decimal(28,10)) as "FSecSumMTOChange",
CAST(null as decimal(28,10)) as "FSecBegQty",
CAST(null as decimal(28,10)) as "FSecEndQty",
DENSE_RANK() OVER (ORDER BY t1.FItemID) AS RANK1,
ROW_NUMBER() over (PARTITION by t1.FDetailID order By t1.FEntryID,t2.FMTONo,t2.FQty desc) RANK2 into #inv
from PORequestEntry t1
left join ICInventory t2 on t1.FItemID = t2.FItemID
left join t_ICItem ti on t1.FItemID = ti.FItemID
left join t_MeasureUnit tm on t1.FUnitID = tm.FMeasureUnitID
left join PORequest t3 on t1.FInterID =t3.FInterID
where t1.FInterID = @finterid
and t1.FPlanMode = '14035'
and t1.FMTONo <> '999999'
and t2.FBatchNo = '999999'
and t2.FMTONo in ('','999999')
and t2.FQty > 0空心泡
and ((ti.FSecUnitID = 0 and t2.FQty > 0) OR (ti.FSecUnitID > 0 and t2.FQty > 0 and t2.FSecQty > 0 ))
更新
更新#inv表的MTO调整单内码
update #inv t FMtoInterID = @maxNum
构建临时表
⽤来存放mto调整单的表头数据
create table #t_MTO(
FID int,
FClassTypeID int,
FTranType int,
FBillNo nvarchar(255),
FDate datetime,
FNote nvarchar(255),
FBillerID int,
FCheckDate datetime,
FEmpID int,
FCheckerID int,
FDeptID int,
FStatus smallint,
FUpStockWhenSave bit,
FPrintCount int,
FSourceBillNo nvarchar(50),
FSourceTranType int
)
⽤来存放mto调整单的表体数据
create table #t_MTOEntry(
FID int,
FIndex int,
FItemID int,
FAuxPropID int,
FBatchNo varchar(255),
FStockID int,
FSPID int,
FBaQty decimal(23,10),
FSecUnitID int,
FUnitID int,
FQty decimal(23,10),
FSecCoefficient decimal(23,10),
FSecQty decimal(23,10),
FChangeQty_Ba decimal(23,10),
FChangeQty decimal(23,10),
FChangeSecQty decimal(23,10),
FKFDate datetime,
FKFPeriod int,
FPeriodDate datetime,
FFromMTONo nvarchar(50),
FToMTONo nvarchar(50),
FChangeBaQty decimal(23,10),
FSelectedProcID int,
FEntrySupply int,
FStockTypeID int,
FMrpNo nvarchar(50)
)
三、计算
declare @R1 int = 1
declare @maxR int = (lect MAX(RANK1) from #inv)
while @R1 <= @maxR begin
if OBJECT_ID('tempdb..#inv2') is not null
drop table #inv2
lect *,DENSE_RANK() OVER (ORDER BY FDetailID) AS RANK3 into #inv2 from #inv where RANK1 = @R1
declare @FSecUnitID int = (lect top(1) FSecUnitID from #inv2 where RANK1 = @R1)
if @FSecUnitID = 0 begin --没有辅助单位的
declare @R2 int = 1
declare @maxR2 int = (lect MAX(RANK2) from #inv2 where RANK3 = 1)
declare @FChaQty decimal(28,10) = (lect t1FQty from #inv2 where RANK2 = 1 and RANK3 = 1)
while @R2 <= @maxR2 begin
t @FChaQty = @FChaQty - (lect t2FQty from #inv2 where RANK2 = @R2 and RANK3 = 1)
update #inv2 t FChaQty = @FChaQty where RANK2 = @R2 and RANK3 = 1
if @FChaQty > 0 update #inv2 t FMTOChange = t2FQty where RANK2 = @R2 and RANK3 = 1
if @FChaQty <=0 update #inv2 t FMTOChange = t2FQty + FChaQty where RANK2 = @R2 and RANK3 = 1
update #inv2 t FSumMTOChange = (lect SUM(FMTOChange) from #inv2 where RANK3 = 1 and FMTOChange > 0) where RANK3 = 1
update #inv2 t FMRPClod = ca when (FSumMTOChange - t1FQty >= 0 ) then 1 el 0 end where RANK3 = 1
update #inv2 t FEndQty = t2FQty - FMTOChange where RANK3 = 1 and FMTOChange > 0
update #inv2 t FEndQty = t2FQty where RANK3 = 1 and FMTOChange <= 0
t @R2 = @R2 +1
end
declare @R3 int = 1
受伤的翅膀
假红牛declare @maxR3 int = (lect MAX(RANK3) from #inv2)
while @R3 <= @maxR3 begin
declare @i int = 1
declare @maxI int = (lect MAX(rank2) from #inv2 where RANK3 = @R3)
while @i <= @maxI begin
update #inv2 t FBegQty = t2FQty where RANK2 = @i and RANK3 = 1
update #inv2 t FBegQty = (lect FEndQty from #inv2 where RANK2 = @i and RANK3 = @R3 and FEndQty >0) where RANK2 = @i and RANK3 = @R3+1 t @i = @i + 1
end
declare @R31 int = 2
while @R31 <= @maxR3 begin
declare @j int = (lect min(RANK2) from #inv2 where FBegQty >0 and RANK3 = @R31)
declare @maxJ int = (lect max(RANK2) from #inv2 where FBegQty >0 and RANK3 = @R31)
declare @FChaQty2 decimal(28,10)= (lect t1FQty from #inv2 where RANK2 = 1 and RANK3 = @R31)
while @j <= @maxJ begin
t @FChaQty2 = @FChaQty2 - (lect FBegQty from #inv2 where RANK2 = @j and RANK3 = @R31)
update #inv2 t FChaQty = @FChaQty2 where RANK2 = @j and RANK3 = @R31
if @FChaQty2 > 0 update #inv2 t FMTOChange = FBegQty where RANK2 = @j and RANK3 = @R31
if @FChaQty2 <=0 update #inv2 t FMTOChange = FBegQty + FChaQty where RANK2 = @j and RANK3 = @R31
update #inv2 t FSumMTOChange = (lect SUM(FMTOChange) from #inv2 where RANK3 = @R31 and FMTOChange >0) where RANK3 = @R31
update #inv2 t FMRPClod = ca when (FSumMTOChange - t1FQty >= 0 ) then 1 el 0 end where RANK3 = @R31
update #inv2 t FEndQty = FBegQty - FMTOChange where RANK3 = @R31 and FMTOChange > 0
update #inv2 t FEndQty = FBegQty where RANK3 = @R31 and FMTOChange <= 0
t @j = @j+1
end
t @R31 = @R31 +1
end
t @R3 = @R3 + 1
end
end
if @FSecUnitID >0 begin --有辅助单位的
declare @R2s int = 1
declare @maxR2s int = (lect MAX(RANK2) from #inv2 where RANK3 = 1)
declare @FSecChaQty decimal(28,10) = (lect FEntrySelfP0139 from #inv2 where RANK2 = 1 and RANK3 = 1)
while @R2s <= @maxR2s begin
网站解决方案t @FSecChaQty = @FSecChaQty - (lect t2FSecQty from #inv2 where RANK2 = @R2s and RANK3 = 1)
update #inv2 t FSecChaQty = @FSecChaQty where RANK2 = @R2s and RANK3 = 1
if @FSecChaQty > 0 update #inv2 t FSecMTOChange = t2FSecQty where RANK2 = @R2s and RANK3 = 1
if @FSecChaQty <=0 update #inv2 t FSecMTOChange = t2FSecQty + FSecChaQty where RANK2 = @R2s and RANK3 = 1
update #inv2 t FSecSumMTOChange = (lect SUM(FSecMTOChange) from #inv2 where RANK3 = 1 and FSecMTOChange > 0) where RANK3 = 1
update #inv2 t FMRPClod = ca when (FSecSumMTOChange - FEntrySelfP0139 >= 0 ) then 1 el 0 end where RANK3 = 1
update #inv2 t FSecEndQty = t2FSecQty - FSecMTOChange where RANK3 = 1 and FSecMTOChange > 0
update #inv2 t FSecEndQty = t2FSecQty where RANK3 = 1 and FSecMTOChange <= 0
update #inv2 t FMTOChange = FSecMTOChange * FSecCoefficient where RANK2 = @R2s and RANK3 = 1
update #inv2 t FSumMTOChange = (lect SUM(FMTOChange) from #inv2 where RANK3 = 1 and FMTOChange > 0) where RANK3 = 1
update #inv2 t FEndQty = t2FQty - FMTOChange where RANK3 = 1 and FMTOChange > 0
update #inv2 t FEndQty = t2FQty where RANK3 = 1 and FMTOChange <= 0
t @R2s = @R2s +1
end
declare @R3s int = 1
declare @maxR3s int = (lect MAX(RANK3) from #inv2)
while @R3s <= @maxR3s begin
declare @is int = 1
declare @maxIs int = (lect MAX(rank2) from #inv2 where RANK3 = @R3s)
while @is <= @maxIs begin
update #inv2 t FSecBegQty = t2FSecQty where RANK2 = @is and RANK3 = 1
update #inv2 t FSecBegQty = (lect FSecEndQty from #inv2 where RANK2 = @is and RANK3 = @R3s and FSecEndQty >0) where RANK2 = @is and RANK3 = @R3s+1 update #inv2 t FBegQty = t2FQty where RANK2 = @is and RANK3 = 1
update #inv2 t FBegQty = (lect FEndQty from #inv2 where RANK2 = @is and RANK3 = @R3s and FEndQty >0) where RANK2 = @is and RANK3 = @R3s+1
t @is = @is + 1
end
declare @R31s int = 2
while @R31s <= @maxR3s begin
declare @js int = (lect min(RANK2) from #inv2 where FSecBegQty >0 and RANK3 = @R31s)
declare @maxJs int = (lect max(RANK2) from #inv2 where FSecBegQty >0 and RANK3 = @R31s)
declare @FSecChaQty2 decimal(28,10)= (lect FEntrySelfP0139 from #inv2 where RANK2 = 1 and RANK3 = @R31s)
while @js <= @maxJs begin
t @FSecChaQty2 = @FSecChaQty2 - (lect FSecBegQty from #inv2 where RANK2 = @js and RANK3 = @R31s)
update #inv2 t FSecChaQty = @FSecChaQty2 where RANK2 = @js and RANK3 = @R31s
if @FSecChaQty2 > 0 update #inv2 t FSecMTOChange = FSecBegQty where RANK2 = @js and RANK3 = @R31s
if @FSecChaQty2 <=0 update #inv2 t FSecMTOChange = FSecBegQty + FSecChaQty where RANK2 = @js and RANK3 = @R31s
update #inv2 t FSecSumMTOChange = (lect SUM(FSecMTOChange) from #inv2 where RANK3 = @R31s and FSecMTOChange >0) where RANK3 = @R31s
update #inv2 t FMRPClod = ca when (FSecSumMTOChange - FEntrySelfP0139 >= 0 ) then 1 el 0 end where RANK3 = @R31s
update #inv2 t FSecEndQty = FSecBegQty - FSecMTOChange where RANK3 = @R31s and FSecMTOChange > 0
update #inv2 t FSecEndQty = FSecBegQty where RANK3 = @R31s and FSecMTOChange <= 0
update #inv2 t FMTOChange = FSecMTOChange * FSecCoefficient where RANK2 = @js and RANK3 = @R31s
update #inv2 t FSumMTOChange = (lect SUM(FMTOChange) from #inv2 where RANK3 = @R31s and FMTOChange > 0 ) where RANK3 = @R31s
update #inv2 t FEndQty = FBegQty - FMTOChange where RANK3 = @R31s and FMTOChange > 0
update #inv2 t FEndQty = FBegQty where RANK3 = @R31s and FMTOChange <= 0
t @js = @js+1
end
t @R31s = @R31s +1
end
t @R3s = @R3s + 1
end
end
lect * from #inv2
四、结束
反写采购申请明细表
update te t
te.FMRPClod = ti.FMRPClod,
te.FCommitQty = ti.FSumMTOChange,
te.FAuxCommitQty = ti.FSumMTOChange / ti.FCoefficient,
te.FSecCommitQty = ca when ti.FSecSumMTOChange IS null then 0 el ti.FSecSumMTOChange end,
te.FOrderQty = ti.FSumMTOChange,
te.FEntrySelfP0140 = ca when ti.FSecUnitID = 0 then ti.FSumMTOChange el ti.FSecSumMTOChange end,
te.FEntrySelfP0141 = 1
from PORequestEntry te,#inv2 ti where te.FDetailID = ti.FDetailID
插⼊MTO单据体临时表
inrt into #t_MTOEntry
lect
FMTOInterID,
'' as "FIndex",
FItemID,
'0' as "FAuxPropID",
FBatchNo,
FStockID,
FStockPlaceID,
FBegQty,
null as "FSecUnitID",
FUnitID,
FBegQty / FCoefficient as "FQty",
FSecCoefficient,
ca when FSecBegQty is null then 0 el FSecBegQty end as "FSecQty",
'0' as FChangeQty_Ba,
FMTOChange / FCoefficient as "FChangeQty",
ca when FSecMTOChange is null then 0 el FSecMTOChange end as "FChangeSecQty",
ca when (FKFDate = '') then null el FKFDate end AS "FKFDate",
FKFPeriod,
ca when (FKFDate = '') then null el FKFDate + FKFPeriod end AS "FPeriodDate",
t2FMTONo,
t1FMTONo,
FMTOChange,
'',
'',
'',
''
from #inv2 where FMTOChange > 0
t @R1 = @R1+1
end
插⼊MTO单据头临时表
inrt into #t_MTO
lect
FMTOInterID,
1107011,
1107011,
汤泉温泉'MTOAUTO'+LTRIM(str(FMTOInterID)),
CONVERT(varchar(10),getdate(),23)+' 00:00:00.000',
'',
16394,
GETDATE(),
2649,
16394,
277,
1,
0,
0,
FBillNo,
70
from #inv2
插⼊数据表
inrt into t_MTOChange lect * from #t_MTO
inrt into t_MTOChangeEntry lect * from #t_MTOEntry
插⼊审批流
Inrt Into ICClassCheckRecords1107011(FPage,FBillID,FBillEntryID,FBillNo, FBillEntryIndex,FCheckLevel,FCheckLevelTo,FMode,FCheckMan, FCheckIdea,FCheckDate,FDescriptions) Values (1,@maxnum,0,'MTOAUTO'+ltrim(str(@maxnum)),0,-99,-1,0,16394,'',GetDate(),'审核')
重生之妓不如仁Inrt Into ICClassCheckRecords1107011(FPage,FBillID,FBillEntryID,FBillNo, FBillEntryIndex,FCheckLevel,FCheckLevelTo,FMode,FCheckMan, FCheckIdea,FCheckDate,FDescriptions) Values (1,@maxnum,0,'MTOAUTO'+ltrim(str(@maxnum)),0,-1,1,0,16394,'',GetDate(),'审核')
校对即时库存
EXEC CheckInventory
更新采购申请单单据头MTO内码
update PORequest t FChildren = FChildren + 1,FHeadSelfP0134 = @maxNum where FInterID = @finterid
结束
end
