入半成品库.sql

项目管理中,按科目交叉取数报表按科目交叉取数报表

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


alter   Procedure sp_ruku  
(  
 @dDate varchar(20), 
 @cwhcode varchar(20) 
)  
as   

set nocount on  
declare @tmpid varchar(20)
declare @ccode varchar(20) 
-- declare @cinvname
declare @p0 int
declare @p1 int
declare @p2 int
set @cwhcode=left(rtrim(ltrim(@cwhcode)),2)

--select cinvcode,cinvname,sum(isnull(bquantity,0)) as iquantity from fc_dutyclassmain   group by cinvcode,cinvname --where cmakedate<=@dDate
select @p0=count(*) from (select cinvcode,cinvname,sum(isnull(bquantity,0)) as iquantity from fc_dutyclassmain  group by cinvcode,cinvname) t1
exec sp_GetId N'', N'001', N'rd', @p0, @P1 output, @P2 output
select @tmpid=convert(varchar(20),@p0)
select @ccode=right('0000000000'+ltrim(rtrim(convert(varchar(20),convert(int,cnumber)+1))),10) from VoucherHistory  Where  CardNumber='0411' and cContent is NULL
update VoucherHistory set cNumber=convert(varchar(20),convert(int,cnumber)+1) Where  CardNumber='0411' and cContent is NULL
-- print @p1
-- print @p2 
set nocount off  
--主表
begin 
--drop table newtable
insert into rdrecord(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,crdcode,cmaker,bpufirst,biafirst,vt_id,
bisstqc,iproorderid,iswfcontrolled) 
values (@p1,1,N'10',N'成品入库',N'库存',@cwhcode,@dDate,@ccode,N'12',N'demo',0,0,63,0,0,0)
end 
--子表
begin
select autoid =IDENTITY(int, 1, 1),@p1 as id,cinvcode,sum(isnull(bquantity,0)) as inum,sum(isnull(bquantity,0)) as iquantity,Null	as	iunitcost	,
Null	as	iprice	,
Null	as	ipunitcost	,
Null	as	ipprice	,
Null	as	cbatch	,
Null	as	cvouchcode	,
Null	as	cfree1	,
Null	as	cfree2	,
Null	as	dsdate	,
Null	as	isoutquantity	,
Null	as	isoutnum	,
Null	as	ifnum	,
Null	as	ifquantity	,
Null	as	dvdate	,
Null	as	itrids	,
Null	as	cposition	,
Null	as	cdefine22	,
Null	as	cdefine23	,
Null	as	cdefine24	,
Null	as	cdefine25	,
Null	as	cdefine26	,
Null	as	cdefine27	,
Null	as	citem_class	,
Null	as	citemcode	,
Null	as	cname	,
Null	as	citemcname	,
Null	as	cfree3	,
Null	as	cfree4	,
Null	as	cfree5	,
Null	as	cfree6	,
Null	as	cfree7	,
Null	as	cfree8	,
Null	as	cfree9	,
Null	as	cfree10	,
Null	as	cbarcode	,
Null	as	inquantity	,
Null	as	innum	,
N'0601'	as	cassunit	,
Null	as	dmadedate	,
Null	as	imassdate	,
Null	as	cdefine28	,
Null	as	cdefine29	,
Null	as	cdefine30	,
Null	as	cdefine31	,
Null	as	cdefine32	,
Null	as	cdefine33	,
Null	as	cdefine34	,
Null	as	cdefine35	,
Null	as	cdefine36	,
Null	as	cdefine37	,
Null	as	impoids	,
Null	as	icheckids	,
Null	as	cbvencode	,
Null	as	cinvouchcode	,
Null	as	ccheckcode	,
Null	as	icheckidbaks	,
Null	as	crejectcode	,
Null	as	irejectids	,
Null	as	ccheckpersoncode	,
Null	as	dcheckdate	,
Null	as	cmassunit	,
Null	as	cmolotcode	,
Null	as	isodid	,
0	as	brelated	,
Null	as	cmworkcentercode	,
Null	as	isotype	,
Null	as	corufts	,
Null	as	cbaccounter	,
1	as	bcosting	,
0	as	bvmiused	,
Null	as	ivmisettlequantity	,
Null	as	ivmisettlenum	,
Null	as	cvmivencode	,
Null	as	iinvsncount
into newtable from fc_dutyclassmain   group by cinvcode,cinvname 

INSERT INTO rdrecords( autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch,cvouchcode,cfree1,cfree2,dsdate,isoutquantity,isoutnum,ifnum,ifquantity,dvdate,itrids,cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,citem_class,citemcode,cname,citemcname,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,cbarcode,inquantity,innum,cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,impoids,icheckids,cbvencode,cinvouchcode,ccheckcode,icheckidbaks,crejectcode,irejectids,ccheckpersoncode,dcheckdate,cmassunit,cmolotcode,isodid,brelated,cmworkcentercode,isotype,corufts,cbaccounter,bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivencode,iinvsncount ) 
select @p2 - autoid as autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch,cvouchcode,cfree1,cfree2,dsdate,isoutquantity,isoutnum,ifnum,ifquantity,dvdate,itrids,cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,citem_class,citemcode,cname,citemcname,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,cbarcode,inquantity,innum,cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,impoids,icheckids,cbvencode,cinvouchcode,ccheckcode,icheckidbaks,crejectcode,irejectids,ccheckpersoncode,dcheckdate,cmassunit,cmolotcode,isodid,brelated,cmworkcentercode,isotype,corufts,cbaccounter,bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivencode,iinvsncount from newtable
 
drop table newtable

end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO