金蝶kingdeeK3系统BOM多级展开,SQL递归查询,数据接口,查询分析报表

skingd*K3饕BOM眼冒RSQLss.tti

ps^ss1kingdeeK3测瀚BOM、SQL踊西、簿ffi潮口、峙sf^^a沸CREATETABLEcCIIfem(FIfemID-nLFNUmbervarchar(200)、FNamevarchar(200)、FMOde_varchar(200)、FunsD-nLFErpc-SID-rluINSERTcc—IEmSELECTLnpool-、用邵1-1-A21ONSELECT2」BC.001.、*mSD1-^-£8^1-P2UNIONSELECT3」BC.002.、*mSD2-^-£8^^-P2UNIONSELECT4」BC.003.、*mSD3-^-£8^3-P3UNIONSELECT5」BC.004.、*mSD4-^-£8^^-P2UNIONSELECT6NC.。。二卅茸至1-1-P1UNIONSELECT7」zcb02-」卅茸壹2-2-P1UNI0NSELECT8LZCOO3-」卅茸壹3-P1UNIONSELECT9LZCOO4-诵皇4-、-卅皇碧g4-M10NSELECT1Q-ZCOO5-」卅茸壹5-rtss5-P1UNIONSELECT1L-ZCOO6-」卅茸壹rtss6-P1UNIONSELECT12NCOO7-诵皇7-、■卅皇兽7-M10NSELECT13NCOO8-」卅茸壹8-rtssP1UNIONSELECT14NCOO9-」卅茸壹9-rtssP1UNIONSELECT15NCO1。-」卅茸壹1。」卅茸壹港参15L1UNIONSELECT16NCO11-诵皇一二卅皇兽11S110NSELECT17NCO12-诵皇12.」卅皇兽12-P1CREATETABLEr+—un_f(FID-nLFNamevarchar(2。))INSERTr+—un一C+SELECTL4-CREATETABLErBOM(FID-nLFBOmNOvarchar(200)、FIEmID-nLFQfydec-ma-(28、10)、Fun-UD-nuINSERTt_BOMSELECT1,'BOM01',1,1,1UNIONSELECT2,'BOM02',2,1,1UNIONSELECT3,'BOM03',3,1,1UNIONSELECT4,'BOM04',4,1,1UNIONSELECT5,'BOM05',5,1,1CREATETABLEt_BOMChild(FIDint,FEntryIDint,FItemIDint,FQtydecimal(28,10),FUnitIDint)INSERTt_BOMChildSELECT1,1,2,1,1UNIONSELECT1,2,3,1,1UNIONSELECT1,3,15,1,1UNIONSELECT1,4,17,1,1UNIONSELECT2,1,6,1,1UNIONSELECT2,2,7,1,1UNIONSELECT3,1,8,1,1UNIONSELECT3,2,9,1,1UNIONSELECT3,3,7,1,1UNIONSELECT3,4,4,1,1UNIONSELECT4,1,10,1,1UNIONSELECT4,2,11,1,1UNIONSELECT4,3,5,1,1/*UNIONSELECT5,1,12,1,1UNIONSELECT5,2,13,1,1UNIONSELECT5,3,14,1,1UNIONSELECT5,4,16,1,1UNIONSELECT5,5,7,1,1*/GOcreateproceduresp_testasbegindeclare@iintdeclare@ttable(Levelint,Codevarchar(40),FItemIDint,FNumbervarchar(20),FNamevarchar(20),FModelvarchar(20),FErpClsIDint,FQtyint,FName1varchar(20),Statusvarchar(20))declare@t1table(idintidentity(1,1),Levelvarchar(10),Codevarchar(40),FItemIDint,FNumbervarchar(20),FNamevarchar(20),FModelvarchar(20),FErpClsIDvarchar(20),FQtyint,FName1varchar(20),Statusvarchar(20))set@i=0insertinto@tselectdistinct@i,right('000'+rtrim(a.FItemID),4),a.FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,b.FQty,c.FName,正常’fromt_IC_Itema,t_BOMb,t_UnitcwhereFUnitID=FUnitIDandb.FUnitID二FIDandnotexists(select1fromt_BOMd,t_BOMChildewhereFID=FIDande.FItemID二a.FItemID)andexists(select1fromt_BOMm,t_BOMChildnwherem.FID=n.FIDandM.FID=a.FItemID)while@@rowcount>0beginset@i=@i+1insertinto@tselect@i,e.Code+right('000'+rtrim(d.FEntryID),4),FItemID,a.FNumber,a.FName,a.FModel,a.FErpClsID,FQty*d.FQty*e.FQty,FName,casewhena.FItemIDin(selectm.FIDfromt_BOMm,t_BOMChildnwherem.FID二n.FID)then'正常’whena.FItemIDin(selectFIDfromt_BOM)then'BOM未建’else''endfromt_IC_Itema,t_BOMb,t_Unitc,t_BOMChildd,@tewhereFUnitID=FUnitIDandb.FUnitID二c.FIDanda.FItemID二d.FItemIDandb.FID=d.FIDandd.FID二e.FItemIDande.level=@i-1endinsertinto@t1(Level,Code,FItemID,FNumber,FName,FModel,FErpClsID,FQty,FName1,Status)selectt.*from(selecttop100percentREPLICATE('.',Level)+rtrim(Level)aslevel,Code,FItemID,FNumber,FName,FModel,(caseFErpClsIDwhen1then'夕卜购'when2then'自制'when3then'委外加T'else"end)asFErpClsID,FQty,FName1,Statusfrom@torderbycode)tselectId,Level,FItemID,FName,FModel,FErpClsID,FQty,FName1,Statusfrom@t1orderbycodeendg。execsp_test/*IdLevelFItemIDFNameFModelFErpClsIDFQtyFName1Status——101产品1产品规格1自制1个正常.12半成品1半成品规格1自制1个正常..26主材料1主材料规格1外购1个..27主材料2主材料规格2外购1个.13半成品2半成品规格2自制1个正常..28主材料3主材料规格3外购1个..29主材料4主材料规格4外购1个..27主材料2主材料规格2外购1个..24半成品3半成品规格3委外加工1个正常...310主材料5主材料规格5外购1个...31