--------------------------------------------------------------------------------------------
能帮我把 t_stock.FName 加进去吗 我做的老是提示FName字段无效 不知道怎么弄
你们用到3个表
t_icitem 物料表
t_stock 仓库表
icbom bom表-- select t_ICItem.fdefaultloc as '编号',t_stock.FName as '仓库' from t_ICItem left outer join t_stock on t_ICItem.fdefaultloc = t_stock.fitemid------------------------------------------------------------------------------------------------
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[zzz_Cz_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
/*
select a.FBomnumber FNumber ,b.FNumber FItemNumber,b.FName ,b.FModel,c.FBOMLevel
from icbom a,t_icitem b ,#data1 c
where a.finterid=c.FBomInterid and b.fitemid =a.fitemid and c.FBOMLevel=0
*/
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))
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,
fnumber int
)
Create Table #Errors
( FIndex int IDENTITY,
FType smallint default(0),
FErrText varchar(355) )
Insert into #mutiParentItem (fbominterid,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom,froutingid)
Select a.finterid, t1.FItemID,a.fqty, 0,0,t1.FErpClsID,t1.FItemID, a.FRoutingid
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='
'
--select @P1, @P2
/*
select isnull(Max(FBOMLevel),0) as FBOMLevel
from #Mutidata
*/
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)
--
)
insert into #Mutidata1(fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid)
select fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid
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
/*
insert into #Mutidata1(FLevelString,fbominterid,fitemid,famount)
select '总计',0,0,sum(famount)
from #Mutidata1
update #Mutidata1 set fbominterid=(select fbominterid from #Mutidata1 where fbomlevel=0)
from #Mutidata1 where FLevelString='总计'
update #Mutidata1 set fitemid=(select fitemid from #Mutidata1 where fbomlevel=0)
from #Mutidata1 where FLevelString='总计'
*/
/*
发件人: Eberhard Hoffmann
发送时间: 2007/09/17 8:55
收件人: Jiang,Yibo; Yeping Ding
抄送: Hongwei Wu
主题: BOM: multi-level explosion
Dear all,
please extend the query „BOM multi level explosion“ and insert the two fields
- planning-strategy
- source
from the Material Basic Data.
This two columns must be shown left from the routing-number.
Best regards
Eberhard Hoffmann
*/
---FQtyMin
---FSecInv
----select * from t_itempropdesc where fitemclassid=4
select a.FIndex,a.FitemID,a.FItemType, a.FBOMLevel ,a.FLevelString as '层次(LEVEL)' ,b.fnumber as '物料代码(Number)',b.fname as '物料名称(Name)',b.f_671 as [English Name],b.f_666 as [German Name],isnull(b.FModel,'') '规格(Spec.)',b.F_664 as FGeNumber,
isnull(b.fchartnumber,'') '图号(Draw No.)',isnull(b.f_669,'') '材质(Material)',isnull(b.f_668,'') '图幅(Size of Drawing)', isnull(c.Fname,'') '单位(Unit)',
case b.FPlanTrategy when 322 then '主生产计划(MPS)' when 324 then '无' when 321 then '物料需求计划(MRP)' end as FPlanTrategy,fs.fsource, b.FQtyMin as [Min order Qty.],b.FSecInv as [Safety Stock],
isnull(tr.Froutingname,ts.froutingname) as '工艺路线(Routing)', ---仓库
tr1.fbillno as fbombillno,a.FNeedQty '用量(Qty)',a.famount '材料成本(Cost)',
--2007-08-10 jiang修改,原来此字段未考虑FErpClsID=2(自制件)并且fmrporder=1(MRP计算产生采购请求项打勾)应显示"采购件"情况
case when b.FErpClsID=2 and b.fmrporder=1 then '外购(Outer Purchase)' else isnull(e.Fname,'') end as '物料属性(Mtr.Att.)',case b.FMRPOrder when 1 then 'Yes' else 'No' end [MRP produce po request], -- 增加默认仓库
'' 'BOM编号(BOM NUM.)', d.fopersn as '工序号（Op. S/N)' ,d.fmachinepos as ' 工位（Position)',d.FEntrySelfZ0134 as '图纸工位(Drawing position number )',d.FEntrySelfZ0133 as '物料指示说明(Material provision indicator)',d.FNote as '备注(Re)',--t_stock.FName as '默认仓库' ,
isnull(i.fname,'') '状态(Status)',a.fbominterid,
tw1.fname fmatresp,isnull(t_stock.fqty,0) fstockqty,isnull(t_stock1.fqty,0) fstockqty1,isnull(t_po.fqty,0) fpoqty,
case b.f_132 when 1 then 'yes' when 0 then 'no' else 'no' end as f_packing,
---新增加字段packing or not
b.FFixLeadTime as [Fixed Lead Time],b.FLeadTime as [Dynamic Lead Time],b.FTotalLeadTime as [Total Leadtime] ,b.FTotalTQQ as [Cum. Lead Time]
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 (select * from t_routing where fdefault=1058) Tr on b.fitemid =tr.fitemid and tr.fparentid<>1008
left join icbom w on a.fbominterid=w.finterid and a.fitemtype=5
left outer join (select * from t_routing where fdefault=1058) ts on ts.finterid=w.froutingid
left outer join t_submessage i on b.fusestate=i.finterid
left outer join icbom w1 on w1.fitemid=a.fitemid and w1.FUseStatus=1072
left outer join (select * from t_routing where fdefault=1058) tr1 on tr1.finterid=w1.froutingid
left outer join
(
SELECT FItemID, CASE WHEN (fdepname IS NOT NULL) AND (fsupname IS NULL)
THEN fdepname WHEN (fdepname IS NULL) AND (fsupname IS NOT NULL)
THEN fsupname WHEN (fdepname IS NULL) AND (fsupname IS NULL)
THEN '' END AS fsource
FROM (SELECT dbo.t_ICItem.FItemID, dbo.t_Department.FName AS FDepName,
dbo.t_Supplier.FName AS FSupName
FROM dbo.t_ICItem LEFT OUTER JOIN
dbo.t_Supplier ON
dbo.t_ICItem.FSource = dbo.t_Supplier.FItemID LEFT OUTER JOIN
dbo.t_Department ON dbo.t_ICItem.FSource = dbo.t_Department.FItemID) v1
) fs on b.fitemid=fs.fitemid
left outer join t_worktype tw1 on tw1.finterid=b.fdefaultworktypeid
left outer join (select fitemid,sum(fqty) fqty from icinventory group by fitemid) t_stock on b.fitemid=t_stock.fitemid
left outer join (select b.fnumber,sum(fqty) fqty from kmcnk3_link.AIS20051208093326.dbo.icinventory a inner join kmcnk3_link.AIS20051208093326.dbo.t_icitem b on a.fitemid=b.fitemid group by b.fnumber) t_stock1 on b.fnumber=t_stock1.fnumber
left outer join (select b.fitemid,sum(fqty-fstockqty) fqty from poorder a inner join poorderentry b on a.finterid=b.finterid where a.fcheckerid>0 and
a.fcancellation=0 and a.fstatus<>3 and b.fmrpclosed=0 group by b.fitemid) t_po on t_po.fitemid=b.fitemid
where 1=1
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[zzz_Cz_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
/*
select a.FBomnumber FNumber ,b.FNumber FItemNumber,b.FName ,b.FModel,c.FBOMLevel
from icbom a,t_icitem b ,#data1 c
where a.finterid=c.FBomInterid and b.fitemid =a.fitemid and c.FBOMLevel=0
*/
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))
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,
fnumber int
)
Create Table #Errors
( FIndex int IDENTITY,
FType smallint default(0),
FErrText varchar(355) )
Insert into #mutiParentItem (fbominterid,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom,froutingid)
Select a.finterid, t1.FItemID,a.fqty, 0,0,t1.FErpClsID,t1.FItemID, a.FRoutingid
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
--select @P1, @P2
/*
select isnull(Max(FBOMLevel),0) as FBOMLevel
from #Mutidata
*/
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)
--
)
insert into #Mutidata1(fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid)
select fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid
from #Mutidata
order by findex
--...
FOperID int default(0)
--
,FName nvarchar(200) null--加入，你的类型是什么，就改为什么
)
insert into #Mutidata1(fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid)
select fentryid,fbominterid,fitemid,fneedqty,fbomlevel,fitemtype,fparentid,flevelstring,fbom,fmaterieltype,foperid
from #Mutidata
order by findex update #Mutidata1 set FName=t.FName from t_stock t where #Mutidata1.FItemID=t.fitemid;--更新
--再选就可以了
--select * from #Mutidata1
你试试：
1、把引用到的表或者对象完全限定名字：db_name.dbo.tablename这样的格式，能免除你很多麻烦。
2、把你的存储过程内容拿出来，作为一个查询而已，执行的时候应该会报错，然后你双击错误的提示，就能大概定位是哪部分了。
定义这个字段啊？
Invalid column name 'FName'.
Insert into #mutiParentItem (fbominterid,FItemID,FNeedQty,FBOMLevel,FParentID,FItemType,FBom,
froutingid,fname)
Select a.finterid, t1.FItemID,a.fqty, 0,0,t1.FErpClsID,t1.FItemID, a.FRoutingid
From
icbom a,t_ICItem t1
Where t1.FItemID = a.fitemid and a.finterid=@finterid
怎么把 t_icitem.fdefaultloc = t_stock.fitemid 添加到上一段啊？
icbom a,t_ICItem t1,t_stock C
Where t1.FItemID = a.fitemid and a.finterid=@finterid and t1.fdefaultloc = C.fitemid