我有个项目DELPHI前台开发的调用一个存贮过程,执行的结果与直接在查询分析器里执行的结果不同,发现有一段代码不起做用
用查询分析器执行的结果是对的,程序里不连续的有部份代码没有执行到,很奇怪,下面是前台代码及后面存贮过程代码,我分三次上传,请高手帮我看看,感谢前台代码:
var
adoquery:tadoquery;
begin
try
form1.ADOConnection1.BeginTrans ;
adoquery:=tadoquery.Create(nil);
adoquery.Connection:=form1.ADOConnection1;
adoquery.Close;
adoquery.SQL.Clear;
adoquery.SQL.Add('exec MyBOMExpand_hgnew141228 '''+formatdatetime('yyyy-MM-dd',datetimepicker1.DateTime)+''','''+formatdatetime('yyyy-MM-dd',datetimepicker2.DateTime)+''','''+formatdatetime('yyyy-MM-dd',datetimepicker3.DateTime)+''', '''+formatdatetime('yyyy-MM-dd',datetimepicker4.DateTime)+'''');
adoquery.ExecSQL;
adoquery.Free;
form1.ADOConnection1.CommitTrans;
showmessage('生成成功!');
except
form1.ADOConnection1.RollbackTrans;
showmessage('生成夫败!');
end;end;
用查询分析器执行的结果是对的,程序里不连续的有部份代码没有执行到,很奇怪,下面是前台代码及后面存贮过程代码,我分三次上传,请高手帮我看看,感谢前台代码:
var
adoquery:tadoquery;
begin
try
form1.ADOConnection1.BeginTrans ;
adoquery:=tadoquery.Create(nil);
adoquery.Connection:=form1.ADOConnection1;
adoquery.Close;
adoquery.SQL.Clear;
adoquery.SQL.Add('exec MyBOMExpand_hgnew141228 '''+formatdatetime('yyyy-MM-dd',datetimepicker1.DateTime)+''','''+formatdatetime('yyyy-MM-dd',datetimepicker2.DateTime)+''','''+formatdatetime('yyyy-MM-dd',datetimepicker3.DateTime)+''', '''+formatdatetime('yyyy-MM-dd',datetimepicker4.DateTime)+'''');
adoquery.ExecSQL;
adoquery.Free;
form1.ADOConnection1.CommitTrans;
showmessage('生成成功!');
except
form1.ADOConnection1.RollbackTrans;
showmessage('生成夫败!');
end;end;
===================================================================================
存贮过程代码:ALTER proc [dbo].[MyBOMExpand_hgnew141228]
@startdate datetime,
@enddate datetime,
@startBackupdate datetime,
@endbackupdate datetime
as
/************************************************
创建临时表
MyBOMExpand_hgnew141228 '2014-11-1','2014-11-30','2014-11-21','2014-12-10'
select * from ##qty where fnumber='TM-ERZ-0Y60000760-1'
************************************************/
If Object_Id( 'Tempdb..#myallexpand ') Is Not NULL
drop table #myallexpand
If Object_Id( 'Tempdb..#myallexpand_mx ') Is Not NULL
drop table #myallexpand_mx
--If Object_Id( 'Tempdb..##qty') Is Not NULL
--drop table ##qty
If Object_Id( 'Tempdb..#qty') Is Not NULL
drop table #qty
declare @month int--定义月份
declare @year int --定义年份
declare @monthint int --定义上月 可用月份
declare @yearint int --定义上月可用年份
set @month=DATEPART(mm,@startdate)
set @year=DATEPART(yy,@startdate)
if(@month=12)
begin
set @monthint=1
set @yearint=@year+1
end
else
begin
set @monthint=@month+1
set @yearint=@year
end
Create Table #myallexpand
(
Fitemid int,
Fnumber nvarchar(50),
SYKY decimal(20,4) default(0),--上月可使用A
BYSL decimal(20,4) default(0),--本月上料B
BYCH decimal(20,4) default(0),--本月出货C
QTCK decimal(20,4) default(0),--本月其他出库
BYTL decimal(20,4) default(0),--本月退料D
LLJC as (SYKY+BYSL-BYCH+BYTL-QTCK),--本月理论结存E=A+B-C-D
bpLPHJ decimal(20,4) default(0),--部品良品合计F1
bpSCBL decimal(20,4) default(0),--部品生产不良F2
LLBL decimal(20,4) default(0),--来料不良F3
cpLPHJ decimal(20,4) default(0),--成品良品合计G1
cpSCBL decimal(20,4) default(0),--成品生产不良G2
bcpLPHJ decimal(20,4) default(0),--半成品良品合计H1
bcpSCBL decimal(20,4) default(0),--半成品生产不良 H2
KHZC decimal(20,4) default(0),--客户资产K
PDHJ as bpLPHJ+bpSCBL+LLBL+ cpLPHJ+cpSCBL+bcpLPHJ+bcpSCBL+KHZC--本月盘点合计L=F1G+H+I+J+K
)
Create Table #myallexpand_mx
(
fnumber_p varchar(255),
fqty_p decimal(20,4) default(0),
ftypeid varchar(100),
Fitemid int,
Fnumber nvarchar(50),
fqty_yl decimal(20,4) default(0),--用量
SYKY decimal(20,4) default(0),--上月可使用A
BYSL decimal(20,4) default(0),--本月上料B
BYCH decimal(20,4) default(0),--本月出货C
QTCK decimal(20,4) default(0),--本月其他出库
BYTL decimal(20,4) default(0),--本月退料D
-- LLJC as (SYKY+BYSL-BYCH+BYTL-QTCK),--本月理论结存E=A+B-C-D
bpLPHJ decimal(20,4) default(0),--部品良品合计F1
bpSCBL decimal(20,4) default(0),--部品生产不良F2
LLBL decimal(20,4) default(0),--来料不良F3
cpLPHJ decimal(20,4) default(0),--成品良品合计G1
cpSCBL decimal(20,4) default(0),--成品生产不良G2
bcpLPHJ decimal(20,4) default(0),--半成品良品合计H1
bcpSCBL decimal(20,4) default(0),--半成品生产不良 H2
KHZC decimal(20,4) default(0),--客户资产K
-- PDHJ as bpLPHJ+bpSCBL+LLBL+ cpLPHJ+cpSCBL+bcpLPHJ+bcpSCBL+KHZC--本月盘点合计L=F1G+H+I+J+K
) Create Table #Qty
(
Fitemid int,
Fnumber nvarchar(255),
fqty decimal(20,4) default(0),
ftypeid varchar(255)
)
--drop table wanghx_qty
-- Create Table wanghx_Qty
-- (
-- fyear int,
-- fmonth int,
-- fnumber_p varchar(255),
-- fqty_p decimal(20,4) default(0),
-- Fitemid int,
-- fqty_yl decimal(20,4) default(0),
-- fqty decimal(20,4) default(0),
-- ftypeid varchar(255)
-- )
delete wanghx_qty where fyear=@year and fmonth=@month
/**************************理论盘点数据******************************/
--================上月可用====================
insert into #myallexpand_mx (Fitemid,SYKY) --上月可用 A
Select t3.fitemid,t3.PDHJ
From PDHJ t3
Where t3.yearint=@year and t3.monthint =@month and cast(pdhj as decimal(20,4))>0 insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)
Select @year,@month,'部品',null,t3.fitemid,0,t3.PDHJ,'上月可用'
From PDHJ t3,t_icitem t4
Where t3.fitemid=t4.fitemid and t3.yearint=@year and t3.monthint =@month and cast(t3.pdhj as decimal(20,4))>0
-- =======本月上料===================
insert into #myallexpand_mx (Fitemid,BYSL) --本月上料 B
Select t3.fitemid,SUM(t2.Fauxqty)
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and (t2.Fdate between @startdate and @enddate) and t2.fauxqty>0
GROUP By t3.FItemID insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)
Select @year,@month,'部品',null,t3.fitemid,null,SUM(t2.Fauxqty),'本月上料'
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate and t2.fauxqty>0
GROUP By t3.FItemID
-- =======本月退料===================
insert into #myallexpand_mx (Fitemid,BYTL)--本月退料 D
Select t3.fitemid,SUM(t2.Fauxqty)
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate and t2.fauxqty<0
GROUP By t3.FItemID
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--本月退料 D
Select @year,@month,'部品',null,t3.fitemid,0,SUM(t2.Fauxqty),'本月退料'
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate and t2.fauxqty<0
GROUP By t3.FItemID
--=======本月出货===================
insert into #Qty (Fitemid,t3.fnumber,fqty,ftypeid)--本月出货bych C
Select t3.fitemid,t3.fnumber,SUM(t2.Fauxqty),'本月出货'
From vwICBill_8 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate
GROUP By t3.FItemID,t3.fnumber
--vwICBill_1外购入库对1,vwICBill_11领料单24 vwICBill_8销售出库 对21 ,vvicbill_16其它出库单 29
--=================贸易出货====================
insert into #Qty (Fitemid,fnumber,fqty,ftypeid) --本月出货备注贸易出货 BYCH
select b.fitemid,b.fnumber,a.fauxqty,'贸易出货'
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate and a.FHeadSelfB0434 like '%贸易出货%'
and (b.fnumber like 'TM-ES%' or b.fnumber like 'TM-EF%') and a.fauxqty<>0 If Object_Id( 'Tempdb..#t1') Is Not NULL
drop table #t1 select identity(int,1,1) as 序号 , t1.* into #t1 from #Qty t1 where ftypeid in ('本月出货','贸易出货')
declare @n int
declare @fqty_b decimal(20,4)
declare @fnumber_b varchar(255)
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t1)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t1 where 序号=@n
insert #myallexpand_mx(fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'本月出货和贸易出货'
set @n=@n+1
end
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid from #myallexpand_mx where ftypeid='本月出货和贸易出货'--=================附件=============
insert into #myallexpand_mx (Fitemid,bych)--本月出货取领料单生产拉线 C
select b.fitemid,a.fauxqty
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate and a.FHeadSelfB0434 like '%附料%'insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--本月出货取领料单生产拉线 C
select @year,@month,'部品',null,b.fitemid,0,a.fauxqty,'附料'
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate and a.FHeadSelfB0434 like '%附料%' ---==================其它出库是部品的==============
insert into #myallexpand_mx (Fitemid,QTCK)--本月其他出库部品
SELECT b.fitemid,a.fauxqty
from vwICBill_16 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate
and b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%' insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--本月出货取领料单生产拉线 C
SELECT @year,@month,'部品',null,b.fitemid,0,a.fauxqty,'其他出库部品'
from vwICBill_16 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate
and b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%'
---==================其它出库是成品的==============
insert into #Qty (Fitemid,fnumber,fqty,ftypeid) --本月出其他出库成品
select b.fitemid,b.fnumber,a.fauxqty,'出其他出库成品'
from vwICBill_16 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate
and (b.fnumber like 'TM-ES%' or b.fnumber like 'TM-EF%') If Object_Id( 'Tempdb..#t2') Is Not NULL
drop table #t2
select identity(int,1,1) as 序号 , t1.* into #t2 from #Qty t1 where ftypeid in ('出其他出库成品')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t2)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t2 where 序号=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'出其他出库成品'
set @n=@n+1
END insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid from #myallexpand_mx where ftypeid='出其他出库成品'
--**********************************************
--部品F1F2F3
--*******************************************
--==================来料不良=====================
insert into #myallexpand_mx (Fitemid,LLBL)--来料不良F1
select i.fitemid,i.fqtyact from icinvbackup i
join t_stock t
on i.fstockid=t.fitemid and t.FProperty=12
join t_icitem t2 on t2.fitemid=i.fitemid
where t2.fnumber not like 'TM-ES%' and t2.fnumber not like 'TM-EF%' and i.fqtyact>0 and i. fdatebackup between @startBackupdate and @endbackupdate insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--来料不良F1
select @year,@month,'部品',null,i.fitemid,0,i.fqtyact,'来料不良' from icinvbackup i
join t_stock t
on i.fstockid=t.fitemid and t.FProperty=12
join t_icitem t2 on t2.fitemid=i.fitemid
where t2.fnumber not like 'TM-ES%' and t2.fnumber not like 'TM-EF%' and i.fqtyact>0 and i. fdatebackup between @startBackupdate and @endbackupdate
--==================来料不良有半成品成品的化料 =====================
insert into #qty (Fitemid,fnumber,fqty,ftypeid)--来料不良 F1 有半成品成品的化料
select i.fitemid,t2.fnumber,i.fqtyact,'半成品来料不良' from icinvbackup i
join t_stock t
on i.fstockid=t.fitemid and t.FProperty=12 and i.fqtyact>0 and fdatebackup between @startBackupdate and @endbackupdate
join t_icitem t2 on t2.fitemid=i.fitemid
where (t2.fnumber like 'TM-ES%' or t2.fnumber like 'TM-EF%') If Object_Id( 'Tempdb..#t3') Is Not NULL
drop table #t3
select identity(int,1,1) as 序号 , t1.* into #t3 from #Qty t1 where ftypeid in ('半成品来料不良')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t3)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t3 where 序号=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,llbl,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'半成品来料不良'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,llbl,ftypeid from #myallexpand_mx where ftypeid='半成品来料不良'
---==========部品合计===========
insert into #myallexpand_mx (Fitemid,bpLPHJ)--bpLPHJ F2 部品良品合计
select i.fitemid ,i.fqtyact from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and (t2.fnumber not like 'TM-ES%' AND t2.fnumber not like 'TM-EF%')
where i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--来料不良 F1 有半成品成品的化料
select @year,@month,'部品',null,i.fitemid,0,i.fqtyact,'部品良品合计' from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and (t2.fnumber not like 'TM-ES%' AND t2.fnumber not like 'TM-EF%')
where i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate -- insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--来料不良 F1 有半成品成品的化料
-- select 2014,11,'部品',null,5496,0,0,'部品良品合计'--================部品生产不良===========
insert into #myallexpand_mx (Fitemid,bpSCBL)--bpSCBL F3 部品生产不良
select b.fitemid,a.fauxqty
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a. fdate between @startdate and @enddate and FHeadSelfB0434 like '%生产不良%'
and (b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%') insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--bpSCBL F3 部品生产不良
select @year,@month,'部品',null,b.fitemid,0,a.fauxqty,'部品生产不良'
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a. fdate between @startdate and @enddate and FHeadSelfB0434 like '%生产不良%'
and (b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%')
--==================半成品H1H2======================
insert into #Qty ( Fitemid,fnumber,fqty,ftypeid)--半成品良品合计H1
select i.fitemid,t2.fnumber,sum(i.fqtyact),'半成品'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and t2.fnumber like 'TM-ES%'
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber
If Object_Id( 'Tempdb..#t4') Is Not NULL
drop table #t4
select identity(int,1,1) as 序号 , t1.* into #t4 from #Qty t1 where ftypeid in ('半成品')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t4)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t4 where 序号=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,bcpLPHJ,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'半成品'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bcpLPHJ,ftypeid from #myallexpand_mx where ftypeid='半成品'
--==========半成品生产不良===============insert into #Qty (Fitemid,fnumber,fqty,ftypeid)--半成品生产不良 H2
select b.fitemid,b.fnumber,a.fauxqty,'半成品生产不良' --取领料单中备注附料跟半成品成品的物料
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and fdate between @startdate and @enddate and FHeadSelfB0434 like '%生产不良%'
and (b.fnumber like 'TM-ES%' or b.fnumber like 'TM-EF%') If Object_Id( 'Tempdb..#t5') Is Not NULL
drop table #t5
select identity(int,1,1) as 序号 , t1.* into #t5 from #Qty t1 where ftypeid in ('半成品生产不良')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t5)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t5 where 序号=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,bcpSCBL,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'半成品生产不良'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bcpSCBL,ftypeid from #myallexpand_mx where ftypeid='半成品生产不良'
----***********成品************
insert into #Qty ( Fitemid,fnumber,fqty,ftypeid)--成品良品合计G1
select i.fitemid,t2.fnumber,sum(i.fqtyact),'成品良品合计'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and t2.fnumber like 'TM-EF%'
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber If Object_Id( 'Tempdb..#t6') Is Not NULL
drop table #t5
select identity(int,1,1) as 序号 , t1.* into #t6 from #Qty t1 where ftypeid in ('成品良品合计')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t6)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t6 where 序号=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,cpLPHJ,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'成品良品合计'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,cpLPHJ,ftypeid from #myallexpand_mx where ftypeid='成品良品合计'
----===========成品生产不良======insert into #Qty ( Fitemid,fnumber,fqty,ftypeid)--成品生产不良 G2
select i.fitemid,t2.fnumber,sum(i.fqtyact),'成品生产不良'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=11
join t_icitem t2 on i.fitemid=t2.fitemid and t2.fnumber like 'TM-EF%'
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber If Object_Id( 'Tempdb..#t7') Is Not NULL
drop table #t7
select identity(int,1,1) as 序号 , t1.* into #t7 from #Qty t1 where ftypeid in ('成品生产不良')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序号) from #t7)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t7 where 序号=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,cpSCBL,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'成品生产不良'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,cpSCBL,ftypeid from #myallexpand_mx where ftypeid='成品生产不良'
--========客户资产=============
insert into #myallexpand_mx (Fitemid,KHZC)--客户资产
select i.fitemid,sum(i.fqtyact)
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=13
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--客户资产
select @year,@month,'部品',null,i.fitemid,0,sum(i.fqtyact),'客户资产'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=13
join t_icitem t2 on i.fitemid=t2.fitemid
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber
insert into #myallexpand ( Fitemid, Fnumber,SYKY,BYSL,BYCH,QTCK,BYTL,bpLPHJ,LLBL,bpSCBL,bcpLPHJ,bcpSCBL,cpLPHJ,cpSCBL,KHZC )
select t1.fitemid,t2.fnumber,SUM(SYKY),SUM(BYSL),SUM(BYCH),SUM(QTCK),SUM(BYTL),sum(bpLPHJ),SUM(LLBL),SUM(bpSCBL),sum(bcpLPHJ),sum(bcpSCBL),SUM(cpLPHJ),SUM(cpSCBL),SUM(KHZC)
from #myallexpand_mx t1,t_icitem t2
where t1.fitemid=t2.fitemid and t2.fdeleted=0
group by t1.fitemid,t2.fnumber
order by t1.fitemid desc
--**********************************************
--显示报表
--drop table wanghx_hgrp
--********************************************** select identity(int,1,1) as 序号,@year as 年,@month as 月,t1.* into #wanghx_hgrp from (select t1.fnumber as 新编号,t2.fmodel as 规格型号,t2.fname as 海关名称,t3.fname as 单位,SYKY as 上月可用,BYSL AS 本月上料,BYCH as 本月出货,QTCK as 其他出库,BYTL as 本月退料, LLJC as 理论结存,bpLPHJ as 部品良品合计 ,bcpLPHJ as 半成品良品合计,cpLPHJ as 成品良品合计 ,LLBL AS 来料不良 ,bpSCBL as 部品生产不良 ,cpSCBL+bcpSCBL as 成品半成品不良,KHZC as 客户资产, PDHJ as 盘点合计,PDHJ-LLJC AS 差异,t2.FGrossWeight as 单重,t2.FGrossWeight* PDHJ as 总重,t2.F_105 as new公司编号
from #myallexpand t1
join t_icitem t2 on t1.fitemid=t2.fitemid
join t_measureunit t3 on t2.funitid=t3.fitemid ) as t1
delete wanghx_hgrp where 年=@year and 月=@month
insert wanghx_hgrp (序号,年,月,新编号,规格型号,海关名称,单位,上月可用,本月上料,本月出货,其他出库,本月退料,理论结存,部品良品合计,半成品良品合计,成品良品合计,来料不良,部品生产不良,成品半成品不良,客户资产,盘点合计,差异,单重,总重,new公司编号)
select * from #wanghx_hgrp order by 序号
delete from PDHJ where yearint=@yearint and monthint=@monthint insert into PDHJ(fitemid,fnumber,yearint,monthint,PDHJ) --插入上月可用数据
select t1.fitemid,t2.新编号,@yearint,@monthint,t2.盘点合计
from wanghx_hgrp t2,t_icitem t1
where t1.fnumber=t2.新编号 and t2.年=@year and t2.月=@month