金蝶k3即时库存外挂，解决即时库存不能分类汇总功能

spkill

好资源 齐分享 看帖一定要回帖               

dengbing88

楼主帮忙改下嘛，自己改老改错，就不要那个t_Item_3009及和他关联的东西。


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


--exec lookforFitemQty 'and t1.FDate>=''2012-07-01'' and t1.FDate<=''2012-08-01'' and t2.FBatchNo like ''%1%''' ,',t5.FName ','t5.FName as 仓库,',' and t1.仓库=t2.仓库'
ALTER proc [dbo].[lookforFitemQty2] --@FDate=@where @" exec lookforFitemQty '%FDate%', '%FNumber%','%OTHERS%','%condition%','%where%'",
@FDate as nvarchar(500) ,@FNumber nvarchar(500),@groupby as nvarchar(2000),@condition as nvarchar(500),@where as nvarchar(2000)
as
begin
--数据建立分2张表：1 出入库流水账 2 即时库存
--1 出入库流水账
declare @sqlstring1 nvarchar(4000)
declare @sqlstring2 nvarchar(4000)
declare @sqlstring3 nvarchar(4000)
declare @sqlstring4 nvarchar(4000)
declare @sqlstring5 nvarchar(4000)
declare @FNumbertemp nvarchar(500)
set @FNumbertemp=@FNumber
--创建出入库单据的临时表
--select t6.FNumber 物料代码,t6.FName 物料名称,t6.FModel 规格, t1.FDate 日期,
--t2.FBatchNo 批号,t4.F_101 客户, t4.F_102 品牌, t4.F_103 其他, t4.F_104 琴号,t5.FName as 仓库,
--CAST( sum(case when t1.FTranType=1 then t2.FQty else 0 end) as decimal(18,1)) 外购入库,
--CAST( sum(case when t1.FTranType=2 then t2.FQty else 0 end) as decimal(18,1)) 产品入库,
--CAST( sum(case when t1.FTranType=10 then t2.FQty else 0 end) as decimal(18,1)) 其他入库,
--CAST( sum(case when t1.FTranType=5 then t2.FQty else 0 end) as decimal(18,1)) 委外加工入库,
--CAST( sum(case when t1.FTranType in (1,2,10,5) then t2.FQty else 0 end) as decimal(18,1)) 入库合计,
--CAST( sum(case when t1.FTranType=21 then t2.FQty else 0 end) as decimal(18,1)) 销售出库,
--CAST( sum(case when t1.FTranType=29 then t2.FQty else 0 end) as decimal(18,1)) 其他出库单,
--CAST( sum(case when t1.FTranType=24 then t2.FQty else 0 end) as decimal(18,1)) 生产领料单,
--CAST( sum(case when t1.FTranType=28 then t2.FQty else 0 end) as decimal(18,1)) 委外加工出库单,
--CAST( sum(case when t1.FTranType in (21,29,24,28) then t2.FQty else 0 end) as decimal(18,1)) 出库合计
create table #temp1(物料代码 varchar(50),物料名称 varchar(50),规格 varchar(50),日期 datetime,批号 varchar(50),客户 varchar(50),品牌 varchar(50),
其他 varchar(50),琴号 varchar(50),仓库 varchar(50),外购入库 decimal(18,1),产品入库 decimal(18,1),其他入库 decimal(18,1),委外加工入库 decimal(18,1)
,入库合计 decimal(18,1),销售出库 decimal(18,1),其他出库单 decimal(18,1),生产领料单 decimal(18,1),委外加工出库单 decimal(18,1),出库合计 decimal(18,1))

--根据条件插入出入库单据的（汇总）数据
--改写条件中的对应表字段
select @FDate=REPLACE(@FDate,'日期','t1.FDate')
select @FNumber = REPLACE(@FNumber,'物料代码','t6.FNumber')
Set @sqlstring1='insert into #temp1
select t6.FNumber 物料代码,t6.FName 物料名称,t6.FModel 规格, t1.FDate 日期,
t2.FBatchNo 批号,t4.F_101 客户, t4.F_102 品牌, t4.F_103 其他, t4.F_104 琴号,t5.FName as 仓库,
CAST( sum(case when t1.FTranType=1 then t2.FQty else 0 end) as decimal(18,1)) 外购入库,
CAST( sum(case when t1.FTranType=2 then t2.FQty else 0 end) as decimal(18,1)) 产品入库,
CAST( sum(case when t1.FTranType=10 then t2.FQty else 0 end) as decimal(18,1)) 其他入库,
CAST( sum(case when t1.FTranType=5 then t2.FQty else 0 end) as decimal(18,1)) 委外加工入库,
CAST( sum(case when t1.FTranType in (1,2,10,5) then t2.FQty else 0 end) as decimal(18,1)) 入库合计,
CAST( sum(case when t1.FTranType=21 then t2.FQty else 0 end) as decimal(18,1)) 销售出库,
CAST( sum(case when t1.FTranType=29 then t2.FQty else 0 end) as decimal(18,1)) 其他出库单,
CAST( sum(case when t1.FTranType=24 then t2.FQty else 0 end) as decimal(18,1)) 生产领料单,
CAST( sum(case when t1.FTranType=28 then t2.FQty else 0 end) as decimal(18,1)) 委外加工出库单,
CAST( sum(case when t1.FTranType in (21,29,24,28) then t2.FQty else 0 end) as decimal(18,1)) 出库合计
from ICStockBill t1
left join ICStockBillEntry t2 on t1.FInterID=t2.FInterID
left join t_AuxItem t3 on t2.FAuxPropID=t3.FItemID
left join t_Item_3009 t4 on t4.FItemID=t3.FItemID
left join t_ICItem t6 on t6.FItemID=t2.FItemID
left join t_stock t5 on t5.FItemID=ISNULL( t2.FDCStockID,0 )+ isnull(t2.FSCStockID ,0)
where t1.FTranType in (1,2,10,21,29,24) and t1.FCheckerID>0 '+@FDate+ @FNumber +@where+'
group by t6.FNumber,t6.FName,t6.FModel,t1.FDate , t2.FBatchNo ,t4.F_101 , t4.F_102 , t4.F_103 , t4.F_104 ,t5.FName '

exec sp_executesql @sqlstring1
----------------------------------------
set @sqlstring2=
'select 物料代码, 物料名称,规格,
sum(外购入库) as 外购入库,
sum(产品入库) as 产品入库,
sum(其他入库) as 其他入库,
sum(委外加工入库) as 委外加工入库,
sum(销售出库) as 销售出库,
sum(其他出库单) as 其他出库单,
sum(生产领料单) as 生产领料单,
sum(委外加工出库单) as 委外加工出库单,
sum(入库合计) as 入库合计,sum(出库合计) as 出库合计 '
+@groupby+
' into #temp3 from #temp1
group by 物料代码, 物料名称,规格'+@groupby

-------------------------批号, 客户, 品牌, 其他, 琴号, 仓库,------------------------------------------------
---------------------------------------即时库存信息表 ----------------------------------------------------------
--建立即时库存临时表
create table #temp2 (物料代码 varchar(50),物料名称 varchar(50),规格 varchar(50),批号 varchar(50),客户 varchar(50),品牌 varchar(50),其他 varchar(50),琴号 varchar(50),仓库 varchar(50),即时库存 decimal(18,1))

Set @sqlstring5='
insert into #temp2
select t22.FNumber 物料代码,t22.FName as 物料名称,t22.FModel as 规格,
t2.FBatchNo 批号,t4.F_101 客户, t4.F_102 品牌, t4.F_103 其他, t4.F_104 琴号,t5.FName 仓库,
cast(sum(t2.FQty) as decimal(18,1)) 即时库存
from ICInventory t2
left join t_ICItem t22 on t2.FItemID=t22.FItemID
left join t_Item_3009 t4 on t4.FItemID=t2.FAuxPropID
left join t_stock t5 on t5.FItemID=t2.FStockID where 1=1 '
+@where+'
group by t22.FNumber,t2.FBatchNo,t4.F_101,t4.F_102 ,t4.F_103 , t4.F_104 ,t5.FName,t22.FName,t22.FModel'

exec sp_executesql @sqlstring5

set @sqlstring3='
select 物料代码,物料名称,规格,sum(即时库存) as 即时库存 '+@groupby +
' into #temp4 from #temp2 '
+' group by 物料代码,物料名称,规格 '+@groupby+
'
select t2.*,
t1.外购入库,t1.产品入库,t1.其他入库,t1.委外加工入库,t1.销售出库,t1.其他出库单,t1.生产领料单,t1.委外加工出库单,t1.入库合计,t1.出库合计,
t2.即时库存-(t1.入库合计-t1.出库合计) as 期初 into #temp5 from #temp4 t2
left join #temp3 t1 on t1.物料代码=t2.物料代码 where 1=1 '
+@condition
-----------------------------------------------------------------------------
set @sqlstring4='
select * from #temp5 where 1=1 '
+@FNumbertemp +
' order by 物料代码'

---------------------
--'select 物料代码, 物料名称,规格,
--sum(外购入库) as 外购入库,
--sum(产品入库) as 产品入库,
--sum(其他入库) as 其他入库,
--sum(委外加工入库) as 委外加工入库,
--sum(销售出库) as 销售出库,
--sum(其他出库单) as 其他出库单,
--sum(生产领料单) as 生产领料单,
--sum(委外加工出库单) as 委外加工出库单,
--sum(入库合计) as 入库合计,sum(出库合计) as 出库合计 '

declare @sql nvarchar(4000)
set @sql = @sqlstring2 + @sqlstring3+@sqlstring4
exec sp_executesql @sql
end
--exec lookforFitemQty '','',''
--exec lookforFitemQty ' where 1=1 and 物料代码 >= ''01.01.01.01.1001'' and 物料代码 <= ''01.01.01.01.1001''','',''

dengbing88

楼主：你就不能改好呀，我们这些菜鸟根本改不了， 不是这错就是没有数据啥的，你给改好行不？

yldhjdl

下载了看看怎么样1111111111111111111111111111

hxp.china.sh

查询无效，不能用呀，K3控制台添加进去后，没法查询呀，一查就报错!!!

qianco

相当不错，能提供源代码多好相当不错，能提供源代码多好

hxp.china.sh

冒视12.x的版本不能用..报错的呀!!!

jiage1980

这个东东正是需要的,很好很强大!

yinyushan

好东西，以后建议多发这类技术性资源啊！

yikedashu

不错的东西，就是太贵了！！！