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
推荐阅读