BOM 反查SQL 原创
金蝶云社区-疯狂的石头
疯狂的石头
6人赞赏了该文章 1,427次浏览 未经作者许可,禁止转载编辑于2021年02月01日 22:46:09


CREATE TABLE #TM_ENG_BOMEXPANDBSOURCE (FID BIGINT NULL DEFAULT 0, FENTRYID BIGINT NULL DEFAULT 0, FMATERIALID BIGINT NULL DEFAULT 0, FAUXPROPID BIGINT NULL DEFAULT 0)


CREATE TABLE #TM_ENG_BOMEXPANDBRESULT (FID BIGINT NULL DEFAULT 0, FENTRYID BIGINT NULL DEFAULT 0, FMATERIALID BIGINT NULL DEFAULT 0, FBOMID BIGINT NULL DEFAULT 0, FBOMENTRYID BIGINT NULL DEFAULT 0, FBOMLEVEL BIGINT NULL DEFAULT 0, FMATERIALTYPE NVARCHAR (255) NULL DEFAULT '', FISEXPANDED CHAR (1) NULL DEFAULT '0', FPARENTENTRYID BIGINT NULL DEFAULT 0, FNUMERATOR DECIMAL (23, 10) NULL DEFAULT 0, FDENOMINATOR DECIMAL (23, 10) NULL DEFAULT 0, FFIXSCRAPQTY DECIMAL (23, 10) NULL DEFAULT 0, FSCRAPRATE DECIMAL (23, 10) NULL DEFAULT 0, FBOMENTRYGUID NVARCHAR (255) NULL, FCHILDUNITID BIGINT NULL DEFAULT 0, FCHILDQTY DECIMAL (23, 10) NULL DEFAULT 0, FDOSAGETYPE NVARCHAR (255) NULL DEFAULT '', FAUXPROPID BIGINT NULL DEFAULT 0, FUNITID BIGINT NULL DEFAULT 0, FBOMCHILDMATERIALID BIGINT NULL DEFAULT 0, FERPCLSID BIGINT NULL DEFAULT 0, FPATHENTRYID NVARCHAR (255) NULL, FEFFECTDATE DATETIME NULL, FEXPIREDATE DATETIME NULL, FUPPERCHILDUNITID BIGINT NULL DEFAULT 0, FCHILDQTYUNITID BIGINT NULL DEFAULT 0, FBATCHQTY DECIMAL (23, 10) NULL DEFAULT 0, FBOMENTRYCONTROL BIGINT NULL DEFAULT 0, FPOSITIONNO NVARCHAR (2000) NULL)

 --插入物料ID

INSERT INTO #TM_ENG_BOMEXPANDBSOURCE with(tablock)  (FENTRYID, FMATERIALID, FAUXPROPID) VALUES (1, 100136, 0)


 

 DECLARE @FBomMaxLevel bigint 


 declare @FBomLevel bigint 


 declare @FLastBomLevel bigint 


 declare @FUnExpandBomRowCount bigint 


 declare @FDefCalId bigint 


 declare @FSeqId bigint 


 declare @FSeqField bigint 



begin

set @FBomMaxLevel=30


set @FBomLevel=1


set @FLastBomLevel=0


INSERT INTO #TM_ENG_BOMEXPANDBRESULT with(tablock)  (FMATERIALID, FBOMID, FBOMENTRYID, FBOMLEVEL, FMATERIALTYPE, FISEXPANDED, FPARENTENTRYID, FNUMERATOR, FDENOMINATOR, FFIXSCRAPQTY, FSCRAPRATE, FBOMENTRYGUID, FCHILDUNITID, FCHILDQTY, FDOSAGETYPE, FAUXPROPID, FUNITID, FBOMCHILDMATERIALID, FERPCLSID, FPATHENTRYID, FEFFECTDATE, FEXPIREDATE, FUPPERCHILDUNITID, FCHILDQTYUNITID, FBATCHQTY, FBOMENTRYCONTROL, FPOSITIONNO) SELECT ISNULL(v1.FMaterialId, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL('', ''), ISNULL(1, '0'), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL('', ''), ISNULL(0, 0), ISNULL(0, 0), ISNULL('', ''), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL('', ''), ISNULL(NULL, NULL), ISNULL(NULL, NULL), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL(0, 0), ISNULL('', '') FROM (SELECT * FROM #TM_ENG_BOMEXPANDBSOURCE) v1;


 select @FSeqId=max(isnull(FEntryId,0))+1 from #TM_ENG_BOMEXPANDBRESULT 

 update #TM_ENG_BOMEXPANDBRESULT set @FSeqField=FEntryId=@FSeqId,@FSeqId=@FSeqId+1 

 where isnull(FEntryId,0)<=0;


INSERT INTO #TM_ENG_BOMEXPANDBRESULT with(tablock)  (FMATERIALID, FBOMID, FBOMENTRYID, FBOMLEVEL, FMATERIALTYPE, FISEXPANDED, FPARENTENTRYID, FNUMERATOR, FDENOMINATOR, FFIXSCRAPQTY, FSCRAPRATE, FBOMENTRYGUID, FCHILDUNITID, FCHILDQTY, 

FDOSAGETYPE, FAUXPROPID, FUNITID, FBOMCHILDMATERIALID, FERPCLSID, FPATHENTRYID, FEFFECTDATE, FEXPIREDATE, FUPPERCHILDUNITID, FCHILDQTYUNITID, FBATCHQTY, FBOMENTRYCONTROL, FPOSITIONNO) SELECT ISNULL(v2.FMaterialId, 0), ISNULL(v2.FId, 0), 

ISNULL(u1.FEntryId, 0), ISNULL(1, 0), ISNULL(u1.FMaterialType, ''), ISNULL(0, '0'), ISNULL(w.FEntryId, 0), ISNULL(CASE  WHEN (ISNULL(v3.FNumerator, 0) > 0) THEN v3.FNumerator ELSE u1.FNumerator END, 0), ISNULL(CASE  WHEN (ISNULL(v3.FDenominator, 0) > 0) THEN (v3.FDenominator * v2.FQty) ELSE (u1.FDenominator * v2.FQty) END, 0), ISNULL(CASE  WHEN (ISNULL(v3.FFixScrapQty, 0) > 0) THEN v3.FFixScrapQty ELSE u1.FFixScrapQty END, 0), ISNULL(CASE  WHEN (ISNULL(v3.FScrapRate, 0) > 0) THEN v3.FScrapRate ELSE u1.FScrapRate END, 0), ISNULL(u1.FGuID, ''), ISNULL(u1.FUnitId, 0), ISNULL(CONVERT(DECIMAL(23,10), (1 * (ISNULL(CASE  WHEN (ISNULL(v3.FNumerator, 0) > 0) THEN v3.FNumerator ELSE u1.FNumerator END, 0) / ISNULL(CASE  WHEN (ISNULL(v3.FDenominator, 0) > 0) THEN (v3.FDenominator * v2.FQty) ELSE (u1.FDenominator * v2.FQty) END, 0)))), 0), ISNULL(u1.FDosageType, ''), ISNULL(u1.FAUXPROPID, 0), ISNULL(v2.FUnitId, 0), ISNULL(v1.FMaterialId, 0), ISNULL(t0.FErpClsId, 0), ISNULL('', ''), ISNULL(u1.FEffectDate, NULL), ISNULL(u1.FExpireDate, NULL), ISNULL(u1.FUnitId, 0), ISNULL(u1.FUnitId, 0), ISNULL(v2.FQty, 0), ISNULL(0, 0), ISNULL(u1.FPositionNo, '') FROM (SELECT * FROM #TM_ENG_BOMEXPANDBSOURCE) v1 INNER JOIN T_ENG_BOMCHILD u1 ON (v1.FMaterialId = u1.FMaterialId AND v1.FAUXPROPID = u1.FAUXPROPID) INNER JOIN T_ENG_BOM v2 ON u1.Fid = v2.Fid LEFT OUTER JOIN T_ENG_BOMCHILDLOTBASEDQTY v3 ON (((v3.FEntryId = u1.FEntryId AND (1 >= v3.FStartQty)) AND (1 < v3.FEndQty)) AND u1.FDosageType = 3) INNER JOIN T_BD_MATERIALBASE t0 ON v2.FMaterialId = t0.FMaterialId INNER JOIN T_BD_MATERIALBASE t2 ON u1.FMaterialId = t2.FMaterialId INNER JOIN #TM_ENG_BOMEXPANDBRESULT w ON u1.FMaterialId = w.FMaterialId WHERE (((v2.FFORBIDSTATUS <> 'B') AND (u1.FEffectDate <= '2021-02-01 00:00:00')) AND (u1.FExpireDate >= '2021-02-01 00:00:00'));


 select @FSeqId=max(isnull(FEntryId,0))+1 from #TM_ENG_BOMEXPANDBRESULT 

 update #TM_ENG_BOMEXPANDBRESULT set @FSeqField=FEntryId=@FSeqId,@FSeqId=@FSeqId+1 

 where isnull(FEntryId,0)<=0;


UPDATE U1 SET FPathEntryId = ISNULL(CONVERT(NVARCHAR, u1.FEntryId), '') FROM #TM_ENG_BOMEXPANDBRESULT U1  WHERE u1.FBomLevel = @FBomLevel;



 --------处理BOM上查开始 ----------

--在这里偱环展开前,实际是从第二层开始

set @FBomLevel=1


set @FUnExpandBomRowCount=(SELECT TOP 1 ISNULL(count(*), 0) fcount FROM #TM_ENG_BOMEXPANDBRESULT #TM_ENG_BOMEXPANDBRESULT WHERE FIsExpanded = '0')

while @FUnExpandBomRowCount>0 and @FBomLevel<@FBomMaxLevel

begin

INSERT INTO #TM_ENG_BOMEXPANDBRESULT with(tablock)  (FMATERIALID, FBOMID, FBOMENTRYID, FBOMLEVEL, FMATERIALTYPE, FISEXPANDED, FPARENTENTRYID, FNUMERATOR, FDENOMINATOR, FFIXSCRAPQTY, FSCRAPRATE, FBOMENTRYGUID, FCHILDUNITID, FCHILDQTY, 

FDOSAGETYPE, FAUXPROPID, FUNITID, FBOMCHILDMATERIALID, FERPCLSID, FPATHENTRYID, FEFFECTDATE, FEXPIREDATE, FUPPERCHILDUNITID, FCHILDQTYUNITID, FBATCHQTY, FBOMENTRYCONTROL, FPOSITIONNO) SELECT ISNULL(v1.FMaterialid, 0), ISNULL(v1.Fid, 0), 

ISNULL(v2.FEntryId, 0), ISNULL((@FBomLevel + 1), 0), ISNULL(v2.FMaterialType, ''), ISNULL(0, '0'), ISNULL(u1.FEntryId, 0), ISNULL(CONVERT(DECIMAL(23,10), CASE  WHEN (ISNULL(v3.FDetailId, 0) > 0) THEN v3.FNumerator ELSE v2.FNumerator END), 0), ISNULL(CONVERT(DECIMAL(23,10), CASE  WHEN (ISNULL(v3.FDetailId, 0) > 0) THEN (v3.FDenominator * v1.FQty) ELSE (v2.FDenominator * v1.FQty) END), 0), ISNULL(CASE  WHEN (ISNULL(v3.FDetailId, 0) > 0) THEN v3.FFixScrapQty ELSE 

v2.FFixScrapQty END, 0), ISNULL(CASE  WHEN (ISNULL(v3.FDetailId, 0) > 0) THEN v3.FScrapRate ELSE v2.FScrapRate END, 0), ISNULL(v2.FGuID, ''), ISNULL(v2.FUnitId, 0), ISNULL(CONVERT(DECIMAL(23,10), (CONVERT(DECIMAL(23,10), (u1.FCHILDQTY * ISNULL(CONVERT(DECIMAL(23,10), CASE  WHEN (ISNULL(v3.FDetailId, 0) > 0) THEN v3.FNumerator ELSE v2.FNumerator END), 0))) / ISNULL(CONVERT(DECIMAL(23,10), CASE  WHEN (ISNULL(v3.FDetailId, 0) > 0) THEN (v3.FDenominator * v1.FQty) ELSE (v2.FDenominator * v1.FQty) END), 0))), 0), ISNULL(v2.FDosageType, ''), ISNULL(v2.FAUXPROPID, 0), ISNULL(v1.FUNITID, 0), ISNULL(u1.FMaterialId, 0), ISNULL(t0.FErpClsId, 0), ISNULL('', ''), ISNULL(v2.FEffectDate, NULL), ISNULL(v2.FExpireDate, NULL), ISNULL(u1.FUnitId, 0), ISNULL(u1.FChildQtyUnitId, 0), ISNULL(v1.FQty, 0), ISNULL(0, 0), ISNULL(v2.FPositionNo, '') FROM (SELECT * FROM #TM_ENG_BOMEXPANDBRESULT WHERE (FBomLevel = @FBomLevel AND FIsExpanded = '0')) u1 INNER JOIN T_ENG_BOMCHILD v2 ON v2.FMaterialid = u1.FMaterialId INNER JOIN T_ENG_BOM v1 ON v1.FId = v2.FId LEFT OUTER JOIN T_ENG_BOMCHILDLOTBASEDQTY v3 ON (((v3.FEntryId = v2.FEntryId AND (u1.FCHILDQTY >= v3.FStartQty)) AND (u1.FCHILDQTY < v3.FEndQty)) AND v2.FDosageType = 3) INNER JOIN T_BD_MATERIALBASE t0 ON v1.FMaterialId = t0.FMaterialId INNER JOIN T_BD_MATERIALBASE t2 ON v2.FMaterialId = t2.FMaterialId WHERE ((((v1.FFORBIDSTATUS <> 'B') AND (ISNULL(v2.FBomid, 0) = 0 OR ISNULL(v2.FBomid, 0) = ISNULL(u1.FBomid, 0))) AND (v2.FEffectDate <= '2021-02-01 00:00:00')) AND (v2.FExpireDate >= '2021-02-01 00:00:00'));


 select @FSeqId=max(isnull(FEntryId,0))+1 from #TM_ENG_BOMEXPANDBRESULT 

 update #TM_ENG_BOMEXPANDBRESULT set @FSeqField=FEntryId=@FSeqId,@FSeqId=@FSeqId+1 

 where isnull(FEntryId,0)<=0;


UPDATE U1 SET u1.FCHILDQTY = CONVERT(DECIMAL(23,10), (u1.FCHILDQTY * zw.fconvertnumerator)) / zw.fconvertdenominator FROM #TM_ENG_BOMEXPANDBRESULT U1, (SELECT DISTINCT T1.FBomChildMaterialId, T1.FChildUnitID, T1.FUpperChildUnitId, 

T1.FEntryId, ISNULL(TM.FMASTERID, 0) fmasterid, CAST(CASE  WHEN T4.FUNITGROUPID = T6.FUNITGROUPID THEN (CAST((BD1.FCONVERTNUMERATOR * BD2.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) / CAST((BD1.FCONVERTDENOMINATOR * BD2.FCONVERTNUMERATOR) 

AS NUMERIC(23, 10))) ELSE ISNULL((CAST((CAST((CAST((BD1.FCONVERTNUMERATOR * T8.FCONVERTNUMERATOR) AS NUMERIC(23, 10)) * BD2.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) * T9.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) / 

CAST((CAST((CAST((BD1.FCONVERTDENOMINATOR * T8.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) * BD2.FCONVERTNUMERATOR) AS NUMERIC(23, 10)) * T9.FCONVERTNUMERATOR) AS NUMERIC(23, 10))), -1) END AS NUMERIC(23, 10)) rate, ISNULL(T3.FPrecision, 

0) fprecision, ISNULL(T3.FRoundType, '1') froundtype, CASE  WHEN T4.FUNITGROUPID = T6.FUNITGROUPID THEN CAST((BD1.FCONVERTNUMERATOR * BD2.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) ELSE ISNULL(CAST((CAST((CAST((BD1.FCONVERTNUMERATOR * 

T8.FCONVERTNUMERATOR) AS NUMERIC(23, 10)) * BD2.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) * T9.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)), -1.0) END fconvertnumerator, CASE  WHEN T4.FUNITGROUPID = T6.FUNITGROUPID THEN 

CAST((BD1.FCONVERTDENOMINATOR * BD2.FCONVERTNUMERATOR) AS NUMERIC(23, 10)) ELSE ISNULL(CAST((CAST((CAST((BD1.FCONVERTDENOMINATOR * T8.FCONVERTDENOMINATOR) AS NUMERIC(23, 10)) * BD2.FCONVERTNUMERATOR) AS NUMERIC(23, 10)) * 

T9.FCONVERTNUMERATOR) AS NUMERIC(23, 10)), -1.0) END fconvertdenominator, CASE  WHEN T4.FUNITGROUPID = T6.FUNITGROUPID THEN 1 ELSE 0 END isgroup, CASE  WHEN T1.FChildUnitID = T1.FUpperChildUnitId THEN '0' ELSE CASE  WHEN T4.FUNITGROUPID = T6.FUNITGROUPID THEN CASE  WHEN (BD1.FCONVERTTYPE = '1' OR BD2.FCONVERTTYPE = '1') THEN '1' ELSE '0' END ELSE CASE  WHEN ((((BD1.FCONVERTDENOMINATOR * T8.FCONVERTDENOMINATOR) * BD2.FCONVERTNUMERATOR) * T9.FCONVERTNUMERATOR) IS NULL) THEN '-1' ELSE CASE  WHEN (BD1.FCONVERTTYPE = '1' OR BD2.FCONVERTTYPE = '1' OR T8.FCONVERTTYPE = '1' OR T9.FCONVERTTYPE = '1') THEN '1' ELSE '0' END END END END converttype FROM #TM_ENG_BOMEXPANDBRESULT T1 LEFT OUTER JOIN T_BD_MATERIAL TM ON T1.FBomChildMaterialId = TM.FMaterialId LEFT OUTER JOIN T_BD_MATERIALBASE TB ON T1.FBomChildMaterialId = TB.FMaterialId LEFT OUTER JOIN T_BD_UNIT T2 ON T1.FChildUnitID = T2.FUNITID LEFT OUTER JOIN T_BD_UNIT T3 ON T1.FUpperChildUnitId = T3.FUNITID LEFT OUTER JOIN T_BD_UNITGROUP T4 ON T4.FUNITGROUPID = T2.FUNITGROUPID LEFT OUTER JOIN T_BD_UNIT T5 ON (T4.FUNITGROUPID = T5.FUNITGROUPID AND T5.FISBASEUNIT = '1') LEFT OUTER JOIN T_BD_UNITCONVERTRATE BD1 ON ((BD1.FCURRENTUNITID = T1.FChildUnitID AND BD1.FDESTUNITID = T5.FUNITID) AND BD1.FMASTERID = 0) LEFT OUTER JOIN T_BD_UNITGROUP T6 ON T6.FUNITGROUPID = T3.FUNITGROUPID LEFT OUTER JOIN T_BD_UNIT T7 ON (T6.FUNITGROUPID = T7.FUNITGROUPID AND T7.FISBASEUNIT = '1') LEFT OUTER JOIN T_BD_UNITCONVERTRATE BD2 ON ((T1.FUpperChildUnitId = BD2.FCURRENTUNITID AND T7.FUNITID = BD2.FDESTUNITID) AND BD2.FMASTERID = 0) LEFT OUTER JOIN T_BD_UNITCONVERTRATE T8 ON ((T5.FUNITID = T8.FCURRENTUNITID AND TB.FBASEUNITID = T8.FDESTUNITID) AND (T8.FMASTERID = TM.FMASTERID OR T8.FMASTERID = 0)) LEFT OUTER JOIN T_BD_UNITCONVERTRATE T9 ON ((T7.FUNITID = T9.FCURRENTUNITID AND TB.FBASEUNITID = T9.FDESTUNITID) AND (T9.FMASTERID = TM.FMASTERID OR T9.FMASTERID = 0))) zw WHERE ((u1.FBomLevel = (@FBomLevel + 1) AND u1.FEntryId = zw.FEntryId) AND (u1.FUpperChildUnitId > 0));

UPDATE U1 SET u1.FPathEntryId = ISNULL(((v1.FPathEntryId + '\') + CONVERT(NVARCHAR, u1.FEntryId)), '') FROM #TM_ENG_BOMEXPANDBRESULT U1, #TM_ENG_BOMEXPANDBRESULT v1 WHERE ((u1.FBomLevel = (@FBomLevel + 1) AND u1.FIsExpanded = 0) AND u1.FParentEntryId = v1.FEntryId);

UPDATE U1 SET FIsExpanded = ISNULL(1, '') FROM #TM_ENG_BOMEXPANDBRESULT U1  WHERE (u1.FBomLevel = @FBomLevel AND u1.FIsExpanded = 0);


set @FBomLevel=@FBomLevel+1


set @FUnExpandBomRowCount=(SELECT TOP 1 ISNULL(count(*), 0) fcount FROM #TM_ENG_BOMEXPANDBRESULT u1 INNER JOIN t_bd_materialbase t0 ON u1.fmaterialid = t0.fmaterialid WHERE ((u1.FIsExpanded = 0 AND u1.FBomLevel = @FBomLevel) AND (u1.FBomLevel <= 30)))


end;



DELETE u1 FROM #TM_ENG_BOMEXPANDBRESULT u1 WHERE ((FEffectDate > '2021-02-01 00:00:00') OR (FExpireDate < '2021-02-01 00:00:00'));


end;


SELECT FENTRYID id, FMATERIALID materialid2_id, FBOMID bomid_id, FBOMENTRYID bomentryid, FBOMLEVEL bomlevel, FMATERIALTYPE materialtype, FISEXPANDED isexpanded, FPARENTENTRYID parententryid, FNUMERATOR numerator, FDENOMINATOR denominator, FFIXSCRAPQTY fixscrapqty, FSCRAPRATE scraprate, FBOMENTRYGUID bomentryguid, FCHILDUNITID childunitid_id, FCHILDQTY childqty, FDOSAGETYPE dosagetype, FAUXPROPID auxpropid, FUNITID unitid_id, FBOMCHILDMATERIALID bomchildmaterialid_id, FERPCLSID erpclsid, FPATHENTRYID pathentryid, FEFFECTDATE effectdate, FEXPIREDATE expiredate, FUPPERCHILDUNITID upperchildunitid_id, FCHILDQTYUNITID childqtyunitid_id, FBATCHQTY batchqty, FBOMENTRYCONTROL bomentrycontrol, FPOSITIONNO positionno FROM #TM_ENG_BOMEXPANDBRESULT



DROP TABLE #TM_ENG_BOMEXPANDBRESULT

DROP TABLE #TM_ENG_BOMEXPANDBSOURCE


图标赞 6
6人点赞
还没有人点赞,快来当第一个点赞的人吧!
图标打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!