用SQL查询分析实现类似金蝶K3的收发存明细表

栏目: IT技术 · 发布时间: 4年前

内容简介：使用SQL查询分析实现类收发存的报表，原始需求在[buy]判断采购申请单上是否存在需要调整的记录，存在则继续。

使用 SQL 查询分析实现类收发存的报表，原始需求在 另外一篇文章 的第四部分。下图是实现需求。

用SQL查询分析实现类似金蝶K3的收发存明细表

一、准备

删除临时表

[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 (select f1 from (
     select case when (ti.FSecUnitID = 0 and t2.FQty > 0) OR (ti.FSecUnitID > 0 and t2.FQty > 0 and t2.FSecQty > 0 ) then 1 else 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
	select @maxNum = FMaxNum from ICMaxNum where FTableName = 't_MTOChange'
	set @maxNum = @maxNum + 1
	update ICMaxNum set FMaxNum = @maxNum

构建表结构

将采购申请、存货表、需计算的字段进行联结，并构成临时表#inv

select 	
		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.FMRPClosed,
		t1.FPlanMode,
		t1.FEntrySelfP0139, --辅助数量（计算）
		t1.FEntrySelfP0140, --关联数量
		t1.FEntrySelfP0141,  --关联标志
		t2.FStockID,
		t2.FStockPlaceID,
		t2.FKFPeriod,
		t2.FKFDate,
		t2.FQty as "t2FQty",
		t2.FSecQty as "t2FSecQty",
		case when t2.FSecQty > 0 then t2.FQty / t2.FSecQty else 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 set 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,
		FBaseQty decimal(23,10),
		FSecUnitID int,
		FUnitID int,
		FQty decimal(23,10),
		FSecCoefficient decimal(23,10),
		FSecQty decimal(23,10),
		FChangeQty_Base decimal(23,10),
		FChangeQty decimal(23,10),
		FChangeSecQty decimal(23,10),
		FKFDate datetime,
		FKFPeriod int,
		FPeriodDate datetime,
		FFromMTONo nvarchar(50),
		FToMTONo nvarchar(50),
		FChangeBaseQty decimal(23,10),
		FSelectedProcID int,
		FEntrySupply int,
		FStockTypeID int,
		FMrpNo nvarchar(50)
	)

三、计算

declare @R1 int = 1
	declare @maxR int = (select MAX(RANK1) from #inv)
	while @R1 <= @maxR begin
		if OBJECT_ID('tempdb..#inv2') is not null        
		drop table #inv2
		select *,DENSE_RANK() OVER (ORDER BY FDetailID) AS RANK3 into #inv2 from #inv where RANK1 = @R1
		
		declare @FSecUnitID int = (select top(1) FSecUnitID from #inv2 where RANK1 = @R1)
		
		if @FSecUnitID = 0 begin --没有辅助单位的
			declare @R2 int = 1
			declare @maxR2 int = (select MAX(RANK2) from #inv2 where  RANK3 = 1)
			declare @FChaQty decimal(28,10) = (select t1FQty from #inv2 where  RANK2 = 1 and RANK3 = 1)
			while @R2 <= @maxR2 begin
				set @FChaQty = @FChaQty - (select t2FQty from #inv2 where RANK2 = @R2 and RANK3 = 1) 
				update #inv2 set FChaQty = @FChaQty where RANK2 = @R2 and RANK3 = 1
				if @FChaQty > 0 update #inv2 set FMTOChange = t2FQty where RANK2 = @R2 and RANK3 = 1
				if @FChaQty <=0 update #inv2 set FMTOChange = t2FQty + FChaQty where RANK2 = @R2 and RANK3 = 1
				update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = 1 and FMTOChange > 0) where  RANK3 = 1
				update #inv2 set FMRPClosed = case when (FSumMTOChange - t1FQty >= 0 ) then 1 else 0 end where RANK3 = 1
				update #inv2 set FEndQty = t2FQty - FMTOChange where RANK3 = 1 and FMTOChange > 0
				update #inv2 set FEndQty = t2FQty where RANK3 = 1 and FMTOChange <= 0
				set @R2 = @R2 +1
			end

			declare @R3 int = 1
			declare @maxR3 int = (select MAX(RANK3) from #inv2)
			while @R3 <= @maxR3 begin
				
				declare @i int = 1
				declare @maxI int = (select MAX(rank2) from #inv2 where RANK3 = @R3)
				while @i <= @maxI begin
					update #inv2 set FBegQty = t2FQty where RANK2 = @i and RANK3 = 1
					update #inv2 set FBegQty = (select FEndQty from #inv2 where RANK2 = @i and RANK3 = @R3 and FEndQty >0) where RANK2 = @i and RANK3 = @R3+1
					set @i = @i + 1		
				end
				
				declare @R31 int = 2
				while @R31 <= @maxR3 begin
					
					declare @j int = (select min(RANK2) from #inv2 where FBegQty >0 and RANK3 = @R31)
					declare @maxJ int =  (select max(RANK2) from #inv2 where FBegQty >0 and RANK3 = @R31)
					declare @FChaQty2 decimal(28,10)= (select t1FQty from #inv2 where RANK2 = 1 and RANK3 = @R31)
					while @j <= @maxJ begin
						set @FChaQty2 = @FChaQty2 - (select FBegQty from #inv2 where RANK2 = @j and RANK3 = @R31)
						update #inv2 set FChaQty = @FChaQty2 where RANK2 = @j and RANK3 = @R31
						if @FChaQty2 > 0 update #inv2 set FMTOChange = FBegQty where RANK2 = @j and RANK3 = @R31
						if @FChaQty2 <=0 update #inv2 set FMTOChange = FBegQty + FChaQty where RANK2 = @j and RANK3 = @R31
						update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = @R31 and FMTOChange >0) where  RANK3 = @R31 
						update #inv2 set FMRPClosed = case when (FSumMTOChange - t1FQty >= 0 ) then 1 else 0 end where RANK3 = @R31
						update #inv2 set FEndQty = FBegQty - FMTOChange where RANK3 = @R31 and FMTOChange > 0
						update #inv2 set FEndQty = FBegQty where RANK3 = @R31 and FMTOChange <= 0
						set @j = @j+1
					end 
					set @R31 = @R31 +1
				end
				set @R3 = @R3 + 1		
			end	
		end
		
		if @FSecUnitID >0 begin --有辅助单位的
			declare @R2s int = 1
			declare @maxR2s int = (select MAX(RANK2) from #inv2 where  RANK3 = 1)
			declare @FSecChaQty decimal(28,10) = (select FEntrySelfP0139 from #inv2 where  RANK2 = 1 and RANK3 = 1)
			while @R2s <= @maxR2s begin
				set @FSecChaQty = @FSecChaQty - (select t2FSecQty from #inv2 where RANK2 = @R2s and RANK3 = 1) 
				update #inv2 set FSecChaQty = @FSecChaQty where RANK2 = @R2s and RANK3 = 1
				if @FSecChaQty > 0 update #inv2 set FSecMTOChange = t2FSecQty where RANK2 = @R2s and RANK3 = 1
				if @FSecChaQty <=0 update #inv2 set FSecMTOChange = t2FSecQty + FSecChaQty where RANK2 = @R2s and RANK3 = 1
				update #inv2 set FSecSumMTOChange = (select SUM(FSecMTOChange) from #inv2 where RANK3 = 1 and FSecMTOChange > 0) where RANK3 = 1
				update #inv2 set FMRPClosed = case when (FSecSumMTOChange - FEntrySelfP0139 >= 0 ) then 1 else 0 end where RANK3 = 1
				update #inv2 set FSecEndQty = t2FSecQty - FSecMTOChange where RANK3 = 1 and FSecMTOChange > 0
				update #inv2 set FSecEndQty = t2FSecQty where RANK3 = 1 and FSecMTOChange <= 0
				
				update #inv2 set FMTOChange = FSecMTOChange * FSecCoefficient where RANK2 = @R2s and RANK3 = 1
				update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = 1 and FMTOChange > 0) where RANK3 = 1
				update #inv2 set FEndQty = t2FQty - FMTOChange where RANK3 = 1 and FMTOChange > 0
				update #inv2 set FEndQty = t2FQty where RANK3 = 1 and FMTOChange <= 0
				set @R2s = @R2s +1
			end

			declare @R3s int = 1
			declare @maxR3s int = (select MAX(RANK3) from #inv2)
			while @R3s <= @maxR3s begin
				
				declare @is int = 1
				declare @maxIs int = (select MAX(rank2) from #inv2 where RANK3 = @R3s)
				while @is <= @maxIs begin
					update #inv2 set FSecBegQty = t2FSecQty where RANK2 = @is and RANK3 = 1
					update #inv2 set FSecBegQty = (select FSecEndQty from #inv2 where RANK2 = @is and RANK3 = @R3s and FSecEndQty >0) where RANK2 = @is and RANK3 = @R3s+1
					
					update #inv2 set FBegQty = t2FQty where RANK2 = @is and RANK3 = 1
					update #inv2 set FBegQty = (select FEndQty from #inv2 where RANK2 = @is and RANK3 = @R3s and FEndQty >0) where RANK2 = @is and RANK3 = @R3s+1
					set @is = @is + 1		
				end
				
				declare @R31s int = 2
				while @R31s <= @maxR3s begin
					
					declare @js int = (select min(RANK2) from #inv2 where FSecBegQty >0 and RANK3 = @R31s)
					declare @maxJs int =  (select max(RANK2) from #inv2 where FSecBegQty >0 and RANK3 = @R31s)
					declare @FSecChaQty2 decimal(28,10)= (select FEntrySelfP0139 from #inv2 where RANK2 = 1 and RANK3 = @R31s)
					while @js <= @maxJs begin
						set @FSecChaQty2 = @FSecChaQty2 - (select FSecBegQty from #inv2 where RANK2 = @js and RANK3 = @R31s)
						update #inv2 set FSecChaQty = @FSecChaQty2 where RANK2 = @js and RANK3 = @R31s
						if @FSecChaQty2 > 0 update #inv2 set FSecMTOChange = FSecBegQty where RANK2 = @js and RANK3 = @R31s
						if @FSecChaQty2 <=0 update #inv2 set FSecMTOChange = FSecBegQty + FSecChaQty where RANK2 = @js and RANK3 = @R31s
						update #inv2 set FSecSumMTOChange = (select SUM(FSecMTOChange) from #inv2 where RANK3 = @R31s and FSecMTOChange >0) where RANK3 = @R31s
						update #inv2 set FMRPClosed = case when (FSecSumMTOChange - FEntrySelfP0139 >= 0 ) then 1 else 0 end where RANK3 = @R31s
						update #inv2 set FSecEndQty = FSecBegQty - FSecMTOChange where RANK3 = @R31s and FSecMTOChange > 0
						update #inv2 set FSecEndQty = FSecBegQty where RANK3 = @R31s and FSecMTOChange <= 0
						
						update #inv2 set FMTOChange = FSecMTOChange * FSecCoefficient where RANK2 = @js and RANK3 = @R31s
						update #inv2 set FSumMTOChange = (select SUM(FMTOChange) from #inv2 where RANK3 = @R31s and FMTOChange > 0 ) where RANK3 = @R31s
						update #inv2 set FEndQty = FBegQty - FMTOChange where RANK3 = @R31s and FMTOChange > 0
						update #inv2 set FEndQty = FBegQty where RANK3 = @R31s and FMTOChange <= 0
						set @js = @js+1
					end 
					set @R31s = @R31s +1
				end
				set @R3s = @R3s + 1		
			end	
		end
		
		select * from #inv2

四、结束

反写采购申请明细表

update te set 
			te.FMRPClosed = ti.FMRPClosed,
			te.FCommitQty = ti.FSumMTOChange,
			te.FAuxCommitQty = ti.FSumMTOChange / ti.FCoefficient,
			te.FSecCommitQty = case when ti.FSecSumMTOChange IS null then 0 else ti.FSecSumMTOChange end,
			te.FOrderQty = ti.FSumMTOChange,
			te.FEntrySelfP0140 = case when ti.FSecUnitID = 0 then ti.FSumMTOChange else ti.FSecSumMTOChange end,
			te.FEntrySelfP0141 = 1
			from PORequestEntry te,#inv2 ti where te.FDetailID = ti.FDetailID

插入MTO单据体临时表

insert into #t_MTOEntry 
			select 
			FMTOInterID,
			'' as "FIndex",
			FItemID,
			'0' as "FAuxPropID",
			FBatchNo,
			FStockID,
			FStockPlaceID,
			FBegQty,
			null as "FSecUnitID",
			FUnitID,
			FBegQty / FCoefficient as "FQty",
			FSecCoefficient,
			case when FSecBegQty is null then 0 else FSecBegQty end as "FSecQty",
			'0' as FChangeQty_Base,
			FMTOChange / FCoefficient as "FChangeQty",
			case when FSecMTOChange is null then 0 else FSecMTOChange end as "FChangeSecQty",
			case when (FKFDate = '') then null else FKFDate end AS "FKFDate",
			FKFPeriod,
			case when (FKFDate = '') then null else FKFDate + FKFPeriod end AS "FPeriodDate",
			t2FMTONo,
			t1FMTONo,
			FMTOChange,
			'',
			'',
			'',
			''
		    from #inv2 where FMTOChange > 0 
		set @R1 = @R1+1
	end

插入MTO单据头临时表

insert into #t_MTO 
			select 
			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

插入数据表

insert into t_MTOChange select * from #t_MTO
	insert into t_MTOChangeEntry select * from #t_MTOEntry

插入审批流

Insert 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(),'审核')
	Insert 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 set FChildren = FChildren + 1,FHeadSelfP0134 = @maxNum where FInterID = @finterid

结束

end

以上所述就是小编给大家介绍的《用SQL查询分析实现类似金蝶K3的收发存明细表》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
零边际成本社会

零边际成本社会

[美]杰里米·里夫金 / 赛迪研究院专家组 / 中信出版社 / 2014-11-1 / 49.00

在这本书中，《第三次工业革命》作者杰里米•里夫金开创性地探讨了极致生产力、协同共享、产消者、生物圈生活方式等全新的概念，详细地描述了数以百万计的人生产和生活模式的转变。他认为，“产消者”正在以近乎零成本的方式制作并分享自己的信息、娱乐、绿色能源和3D打印产品。他们也通过社交媒体、租赁商、合作组织以极低或零成本的模式分享汽车、住房、服装和其他物品；学生更多地参与到基于零成本模式的开放式网络课程…… ......一起来看看 《零边际成本社会》 这本书的介绍吧!

码农工具
HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2024 码农网 粤ICP备17054400号-3