SBO 常用SQL

产品生产主计划SELECTT0.MsnCodeAS'计划单号',T1.ItemCode,T2.itemnameAS'产品名称',T1.Quantityas'生产数量',T2.onhand,T2.onorderas'已下单',T1.StartDate,T1.EndDate,T1.BaseDocNumas'订单号',T1.BaseDueas'完工日期',T1.ParentCodeas'产品名'FROMOMSNT0INNERJOINMSN3T1ONT0.AbsEntry=T1.AbsEntryINNERJOINOITMT2ONT2.ITEMCODE=T1.ITEMCODEWHERE(T0.MsnCode=N'[%0]'OR'[%0]'='')AND(T1.ItemCode=N'[%1]'OR'[%1]'='')AND(T1.StartDate=CONVERT(DATETIME,'[%2]',112)OR'[%2]'='')ANDT2.TREETYPE='p'材料领用计划SELECTT0.MsnCodeAS'计划单号',T1.ItemCode,T2.itemnameAS'产品名称',T1.Quantityas'生产数量',T2.onhand,t2.Onorderas'已订购',T1.StartDate,T1.EndDate,T1.BaseDocNumas'订单号',T1.BaseDueas'完工日期',T1.ParentCodeas'产品名'FROMOMSNT0INNERJOINMSN3T1ONT0.AbsEntry=T1.AbsEntryINNERJOINOITMT2ONT2.ITEMCODE=T1.ITEMCODEWHERE(T0.MsnCode=N'[%0]'OR'[%0]'='')AND(T1.ItemCode=N'[%1]'OR'[%1]'='')AND(T1.StartDate=CONVERT(DATETIME,'[%2]',112)OR'[%2]'='')ANDT2.TREETYPE='N'多层BOM－－－－－－－－中间表－－－ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[bomt]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[bomt]GOCREATETABLE[dbo].[bomt]([parent_item][nvarchar](20)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[itemname][nvarchar](100)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[sl][numeric](19,6)NULL,[dw][nvarchar](8)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[ck][nvarchar](8)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[Price][numeric](19,6)NULL,[jghb][nvarchar](3)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[pl][smallint]NULL,[ceci][int]NULL,[fhf][char](1)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[scbs][char](2)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[path][nvarchar](800)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[gw][nvarchar](20)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[gx][nvarchar](20)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[cardcode][nvarchar](20)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[gysn][nvarchar](200)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[gc][nvarchar](20)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[hw][nvarchar](20)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[itemwm][nvarchar](200)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[tzs][nvarchar](100)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[scf][nvarchar](50)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[flf][nvarchar](10)COLLATESQL_Latin1_General_CP850_CI_ASNULL,[mjg][numeric](18,6)NULL,[mje][numeric](18,6)NULL,[je][numeric](18,6)NULL,[xjg][numeric](18,6)NULL,[rjg][numeric](18,6)NULL,[rje][numeric](18,6)NULL,[bje][numeric](18,6)NULL)ON[PRIMARY]GO－－－－－－－－－－－存储过程CREATEprocBOM@mjnvarchar(20)asbegindeletefrombomtdeclare@lint,@bzintset@l=0INSERTINTObomt(parent_item,itemname,itemwm,sl,dw,ck,Price,jghb,ceci,scbs,path)selecta.itemcode,a.itemname,a.FrgnName,1,a.InvntryUom,a.DfltWH,a.LstEvlPric,'RMB',@l,a.TreeType,right(space(20)+a.itemcode,20)fromoitmawherea.itemcode=@mj----set@bz=(selectT1.Qauntityfromoittt1wheret1.code=@mj)while@@rowcount0beginset@l=@l+1INSERTINTObomt(parent_item,itemname,itemwm,sl,dw,ck,Price,jghb,ceci,scbs,path,pl,fhf,hw)selecti.itemcode,i.itemname,i.FrgnName,a.Quantity,i.InvntryUom,a.Warehouse,a.Price,a.Currency,@l,i.TreeType,path+','+str(a.ChildNum,3)+right(space(20)+i.itemcode,20),a.PriceList,a.IssueMthd,i.swwfrom(SELECTT1.Father,T1.ChildNum,T1.Code,T1.Quantity/T0.QauntityasQuantity,T1.Warehouse,T1.Price,T1.Currency,T1.PriceList,T1.Comment,T1.LogInstanc,T1.Uom,t1.IssueMthdFROMOITTT0INNERJOINITT1T1ONT0.Code=T1.Father)a,bomtb,oittc,oitmiwhereb.parent_item=c.codeCOLLATESQL_Latin1_General_CP850_CI_ASandc.code=a.fatherCOLLATESQL_Latin1_General_CP850_CI_ASanda.code=i.itemcodeCOLLATESQL_Latin1_General_CP850_CI_ASandb.ceci=@l-1end--=============================================--计算bom的准确用量--=============================================DECLARE@yldecimal(18,6),@pathnvarchar(800),@cdint,@cecintDECLAREbomsCURSORFORSELECTsl,path,len(path),ceciFROMbomtwherescbs='P'andceci0-----获取是生产件的数据，根据层数逐阶展开--DECLARE@countsmallint--SELECT@count=1OPENbomsFETCHNEXTFROMbomsINTO@yl,@path,@cd,@cecWHILE(@@fetch_status-1)BEGIN-------------更新用量UPDATEbomtSETsl=@yl*slWHEREceci=@cec+1------制定下一层，即限制它的第一阶子件，而不往下阶延伸andleft(path,@cd)=@pathFETCHNEXTFROMbomsINTO@yl,@path,@cd,@cecENDCLOSEbomsDEALLOCATEboms--------显示selectcecias层次,parent_itemas物料,itemnameas物料名称,@bz*slas标准设计数量,dwas单位,ckas仓库,fhfas发货方法,scbsasBOM类型frombomtorderbypathendGO