--------------------------------------------------------
能帮我把 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
- SQL code
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo 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' [email protected] select @finterid=finterid from icbom where [email protected] -- 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 [email protected] 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 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 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 '备注(Remark)',--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,