存储过程加查询字段报错
程序代码：set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[zzz_cz_wyh_bom]
@Fnumber nvarchar(255)
AS
declare @finterid int
declare @fitemid int
select @fitemid=fitemid
from t_icitem
where
--fnumber='01.HY.HY-1/116-B-S'
fnumber=@fnumber
select @finterid=finterid
from icbom
where fitemid=@fitemid -- and fusestatus=1072
Create Table #Errorss
( FIndex int IDENTITY, FType smallint default(0),
FBomNumber int default(0), FErrText nvarchar(355) )
Create Table #data1
( FIndex int IDENTITY,
FBomInterid int,
FItemID int null,
FNeedQty decimal(28,8) default(0) null,
FBOMLevel int null, FItemType int null,
FParentID int default(0)null,
FRate decimal(20,8) default(0) null,
FHistory int default(0) null,
FHaveMrp smallint default(0) null,
FLevelString nvarchar(200) null, FBom int
)
exec PlanBomNestingCheck @finterid
Create Table #Mutidata
( FIndex int IDENTITY,
FEntryID INT, FBomInterid int,
FItemID int null,
FNeedQty decimal(28,14) default(0) null,
FBOMLevel int null,
FItemType int null,
FParentID int default(0)null,
FRate decimal(28,14) default(0) null,
FHistory int default(0) null,
FHaveMrp smallint default(0) null,
FLevelString varchar(200) null,
FBom int,
FMaterielType int default(371) null,
FOperID int default(0),
FCZQualityCheck bit)
Create Table #MutiParentItem
(FIndex int IDENTITY,
FEntryID INT default(0),
FBomInterid int,
FItemID int null,
FNeedQty decimal(28,14) default(0) null,
FBOMLevel int null,
FItemType int null,
FParentID int default(0)null,
FRate decimal(28,14) default(0) null,
FHistory int default(0) null,
FHaveMrp smallint default(0) null,
FLevelString varchar(200) null ,
FBom int,
FMaterielType int default(371) null,
FOperID int default(0),
froutingid int null,
FCZQualityCheck bit
)
Create Table #Errors
( FIndex int IDENTITY,
FType smallint default(0),
FErrText varchar(355) )
Insert into #mutiParentItem (fbominterid,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom,froutingid,FCZQualityCheck)
Select a.finterid, t1.FItemID,a.fqty, 0,0,t1.FErpClsID,t1.FItemID, a.FRoutingid,t1.FCZQualityCheck
From icbom a,t_ICItem t1
Where t1.FItemID = a.fitemid and a.finterid=@finterid
declare @P1 int
set @P1=0
declare @P2 char(400)
set @P2=' '
exec PlanMutiBomExpandEx 50, 1, '1900-01-01', '2100-01-01', @P1 output, @P2 output
Create Table #Mutidata1
( FIndex int IDENTITY,
FEntryID INT, FBomInterid int,
FItemID int null,
FNeedQty decimal(28,14) default(0) null,
FBOMLevel int null,
FItemType int null,
FParentID int default(0)null,
fprice decimal(28,14) default(0) null,
famount decimal(28,14) default(0) null,
FLevelString varchar(200) null,
FBom int,
FMaterielType int default(371) null,
FOperID int default(0),
FCZQualityCheck bit)
insert into #Mutidata1(fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid,FCZQualityCheck)
select fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid,FCZQualityCheck
from #Mutidata
order by findex
update t1 set t1.fprice=
(select fprice
from icpurchaseentry t2
where t2.fdetailid= (
select max(t3.fdetailid)
from icpurchaseentry t3
inner join t_icitem t4 on t3.fitemid=t4.fitemid
and t4.fitemid=t1.fitemid) )
from #Mutidata1 t1
where fitemtype=1
update t1 set famount=isnull(isnull(fprice,0)*isnull(fneedqty,0),0)
from #Mutidata1 t1
update t1 set famount=(select sum(famount) from #Mutidata1 )
from #Mutidata1 t1
where fbomlevel=0
Create Table #test
( FIndex int ,
FItemID int null,
fitemtype int null,
FBOMLevel int null,
flevel varchar(255)null,
Fnumber varchar(255) null,
Fname varchar(255) null,
fsm varchar(255) null,
fmodel varchar(255) null,
fdraw varchar(255) null,
Fmaterial varchar(200) null,
Fdrawsize varchar(200) null,
funit varchar(255) null,
frouting varchar(255) null,
Fqty decimal(28,14) default(0) null,
fattid int null,
fattname varchar(255) null,
fbomnumber varchar(255) null,
fremark varchar(255) null,
FCZQualityCheck bit ------------------------------这里是159行报错位置
)
insert into #test
select
a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel ,
a.FLevelString as '层次(LEVEL)' ,b.fnumber as '物料代码(Number)',b.fname as '物料名称(Name)','',
isnull(b.FModel,'') '规格(Spec.)',isnull(b.fchartnumber,'') '图号(Draw No.)',isnull(b.f_669,'') '材质(Material)',
isnull(b.f_668,'') '图幅(Size of Drawing)', isnull(c.Fname,'') '单位(Unit)', tr.Froutingname as '工艺路线(Routing)',
a.FNeedQty '用量(Qty)',--a.famount '材料成本(Cost)',
b.FErpClsID, isnull(e.Fname,'') '物料属性(Mtr.Att.)',a.FCZQualityCheck,
'' 'BOM编号(BOM NUM.)',d.FNote as '备注(Remark)',a.FCZQualityCheck
-- ,
-- isnull(i.fname,'') '状态(Status)'
from #Mutidata1 a
inner join t_icitem b on a.fitemid=b.fitemid
left outer join t_item c on b.funitid=c.fitemid
inner join icbomchild d on a.FBomInterid=d.finterid and a.FOperID=d.FOperID AND a.FEntryID=d.FEntryID and a.FItemID=d.FItemID
left outer join t_submessage e on b.FErpClsID=e.finterid
left outer join t_submessage f on d.FOperID=f.finterid
left outer join t_stock g on d.FStockID=g.FItemID
inner join t_item h on b.fitemid=h.fitemid
left outer join t_routing Tr on b.fitemid =tr.fitemid
left outer join t_submessage i on b.fusestate=i.finterid
where 1=1
union
select
a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel,
a.FLevelString FLevel,b.fnumber FNumber,b.fname FName,'',
isnull(b.FModel,'') FModel,isnull(b.fchartnumber,'') fchartnumber, isnull(b.f_669,''),isnull(f_668,''),
isnull(c.Fname,'') FUnitID,tr.Froutingname,
a.FNeedQty FQty,--a.famount,
b.FErpClsID,isnull(e.Fname,'') FMaterielType,
'' FBomNumber,d.FNote,a.FCZQualityCheck
-- ,
-- isnull(i.fname,'') FUseStatus
from #Mutidata1 a
inner join t_icitem b on a.fitemid=b.fitemid
left outer join t_item c on b.funitid=c.fitemid
inner join iccustbomchild d on a.FBomInterid=d.finterid and a.FItemID=d.FItemID and a.FOperID=d.FOperID AND a.FEntryID=d.FEntryID
left outer join t_submessage e on b.FErpClsID=e.finterid
left outer join t_submessage f on d.FOperID=f.finterid
left outer join t_stock g on d.FStockID=g.FItemID
inner join t_item h on b.fitemid=h.fitemid
left outer join t_routing Tr on b.fitemid =tr.fitemid
left outer join t_submessage i on b.fusestate=i.finterid
where 1=1
--
union
select
a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel,
a.FLevelString FLevel,b.fnumber FNumber,b.fname FName,'',
isnull(b.FModel,'') FModel,isnull(b.fchartnumber,'') fchartnumber, isnull(b.f_669,''),isnull(b.f_668,''),
isnull(c.Fname,'') FUnitID, tr.Froutingname,
a.FNeedQty FQty,--a.famount,
b.FErpClsID,isnull(e.Fname,'') FMaterielType,
d.FBomNumber,d.FNote
-- ,
-- isnull(i.fname,'') FUseStatus
from #Mutidata1 a
inner join t_icitem b on a.fitemid=b.fitemid
left outer join t_item c on b.funitid=c.fitemid
inner join icbom d on a.FBomInterid=d.finterid and a.FItemID=d.FItemID
left outer join t_submessage e on b.FErpClsID=e.finterid
inner join t_item h on b.fitemid=h.fitemid
left outer join t_submessage i on b.fusestate=i.finterid
left outer join t_routing Tr on b.fitemid =tr.fitemid
where 1=1 order by FIndex desc
update a
set fsm= case when b.fstatus=0 then '该物料BOM未审核、未使用,请审核、使用后对其下级BOM进行检查（BOM NO Check and NO Use）'
when b.fstatus=1 and b.fusestatus=1073 then '该物料BOM已审核、未使用,请使用后对其下级BOM进行检查 （BOM is Checked but NO Use）'
else '正常（correct）'
end
from #test a,icbom b
where a.fitemid=b.fitemid and a.fattid in(2,5)
update a
set fsm='该自制件没有BOM，请对该物料建立BOM,并将BOM审核、使用（This Material no BOM,Please create a new BOM , Check and Use this BOM）'
from #test a
where a.fitemid not in (select distinct fitemid from icbom) and a.fattid in(2,5)
update a
set fsm=fsm+'－－该物料存在两个或两个以上的BOM，请进行处理（This material have two or more BOM,Please check it）－－'
from #test a
where a.fitemid in (select fitemid from icbom group by fitemid having count(fitemid)>1)
select FIndex,FitemID,FItemType, FBOMLevel ,
FLevel as '层次(LEVEL)' ,fnumber as '物料代码(Number)',fname as '物料名称(Name)', fsm as '提示（Tips）',
isnull(FModel,'') '规格(Spec.)',isnull(fdraw,'') '图号(Draw No.)',isnull(fmaterial,'') '材质(Material)',
isnull(fdrawsize,'') '图幅(Size of Drawing)', isnull(funit,'') '单位(Unit)', frouting as '工艺路线(Routing)',
fqty '用量(Qty)',--a.famount '材料成本(Cost)',
isnull(fattname,'') '物料属性(Mtr.Att.)',
fbomnumber 'BOM编号(BOM NUM.)',fremark as '备注(Remark)',FCZQualityCheck from #test a
drop table #Errorss
drop table #Errors
drop table #data1
drop table #Mutidata
drop table #Mutidata1
drop table #MutiParentItem
drop table #test消息 213，级别 16，状态 5，过程 zzz_cz_wyh_bom，第 159 行Insert Error: Column name or number of supplied values does not match table definition.
