-- =============================================
-- Author: 利用递归 自定义批量查询BOM查尾阶 替代料用量取0 金额取0
-- Create date: 2022-04-14
-- Description: <Description,,>
-- =============================================
ALTER PROC [dbo].[PROC_BHR_QuerySpecifyBOM_DEFFAULT] AS
BEGIN
/* FSTATUS 0 待处理 1 正在处理 2 处理完成 3 处理失败*/
SET XACT_ABORT ON;
BEGIN TRY
IF OBJECT_ID('tempdb..#Q_BOM') IS NOT NULL
BEGIN
DROP TABLE tempdb..#Q_BOM
END
IF EXISTS(SELECT 1 FROM dbo.BHR_GET_T_ENG_BOM h WHERE h.FSTATUS='1') /*是否正在执行*/
RETURN
BEGIN TRANSACTION
BEGIN
DECLARE @PFNUMBER VARCHAR(30),@PFVERSION NVARCHAR(30),@GUID NVARCHAR(36),@SOSNUMBER INT,@ISREPLACE NVARCHAR(1)
SELECT TOP 1 @GUID=h.FGUID FROM dbo.BHR_GET_T_ENG_BOM h WHERE h.FSTATUS='0'
SELECT h.FGUID,b.FMATERIALNO,b.FSOQTY,b.FISREPLACE INTO #Q_BOM FROM dbo.BHR_GET_T_ENG_BOM h
JOIN dbo.BHR_GET_T_ENG_BOMENTRY b ON b.FGUID = h.FGUID WHERE h.FGUID=@GUID
DECLARE @r INT
SELECT @r=COUNT(*) FROM #Q_BOM
WHILE @r>0
BEGIN
SELECT TOP 1 @GUID=FGUID,@PFNUMBER=FMATERIALNO,@SOSNUMBER=FSOQTY,@ISREPLACE=FISREPLACE FROM #Q_BOM
UPDATE dbo.BHR_GET_T_ENG_BOM SET FSTATUS='1' WHERE FGUID=@GUID
UPDATE dbo.BHR_GET_T_ENG_BOMENTRY SET FSTATUS='1' WHERE FGUID=@GUID AND FMATERIALNO=@PFNUMBER
--declare @p3 dbo.udt_inttable
DECLARE @FPPMATERIALID INT /**/
--insert into @p3 SELECT MAX(FID) FID FROM dbo.T_ENG_BOM WHERE 1=1 AND FFORBIDSTATUS='A' AND FDOCUMENTSTATUS='C'AND FUSEORGID=100436 GROUP BY FMATERIALID
SELECT @PFVERSION=MAX(FNUMBER),@FPPMATERIALID=MAX(FMATERIALID) FROM dbo.T_ENG_BOM WHERE FNUMBER LIKE @PFNUMBER+'%'AND FFORBIDSTATUS='A' AND FDOCUMENTSTATUS='C'AND FUSEORGID=100436
--SELECT @PFVERSION
IF OBJECT_ID('tempdb..#MAXFID') IS NOT NULL
BEGIN
DROP TABLE tempdb..#MAXFID
END
SELECT MAX(FID) FID INTO #MAXFID FROM dbo.T_ENG_BOM WHERE 1=1 AND FFORBIDSTATUS='A' AND FDOCUMENTSTATUS='C'AND FUSEORGID=100436 GROUP BY FMATERIALID
IF OBJECT_ID('tempdb..#T_ENG_BOMITEM') IS NOT NULL
BEGIN
DROP TABLE tempdb..#T_ENG_BOMITEM
END
CREATE TABLE #T_ENG_BOMITEM
(
FITEMS NVARCHAR(300) NULL,/*项次组合*/
FTOPVERSION NVARCHAR(80) NULL,/*顶级BOM版本*/
FLEVEL NVARCHAR(1) NULL,/*BOM层次*/
FVERSION NVARCHAR(30) NULL, /*父项BOM版本*/
FUSEORGID INT NULL,/*使用组织ID*/
FPMATERIALNO NVARCHAR(80) NOT NULL,/*父项物料编码*/
FLOT INT NULL,/*批量*/
FSEQ INT NULL,/*子项序号*/
FREPLACEGROUP INT NULL,/*项次*/
FMATERIALNO NVARCHAR(30) NOT NULL,/*子项物料编码*/
FOLDMATERIALNO NVARCHAR(30) NULL,/*旧物料编码*/
FMATERIALNAME NVARCHAR(255) NULL,/*物料名称*/
FSPECIFICATION NVARCHAR(510) NULL,/*规格型号*/
FERPCLSID NVARCHAR(10) NULL,/*物料属性*/
FMATERIALTYPE NVARCHAR(10) NULL,/*子项物料类型 */
FNUMERATOR DECIMAL(20,10) NULL,/*分子*/
FDENOMINATOR DECIMAL(20,10) NULL,/*分母*/
FFIXSCRAPQTY DECIMAL(20,10) NULL,/* 固定损耗*/
FSCRAPRATE DECIMAL(20,10) NULL,/*变动损耗率*/
FQTY DECIMAL(20,10) NULL,/*用量*/
FEFFECTDATE DATETIME NULL,/*生效日期*/
FEXPIREDATE DATETIME NULL,/*失效日期*/
FISSUETYPE VARCHAR(1) NULL,/*发料方式*/
FOWNERID INT NULL,/*货主 */
FPOSITIONNO NVARCHAR(2000) NULL,/*位置号*/
FCHILDSUPPLYORGID INT NULL, /*供应组织*/
F_BHR_PLMSEQ NVARCHAR(10) NULL,/*PLM序号*/
F_BHR_ECNNO NVARCHAR(30) NULL,/*ECN编号*/
FISSKIP NVARCHAR(1) NULL,/*跳层*/
FISKEYITEM NVARCHAR(1) NULL,/*替代主料*/
FREPLACETYPE NVARCHAR(1) NULL,/*替代方式*/
FREPLACEPOLICY NVARCHAR(100) NULL,/*替代策略*/
FREPLACEPRIORITY NVARCHAR(100) NULL,/*替代优先级*/
FPPMATERIALID INT NOT NULL, /*顶项物料ID*/
FPMATERIALID INT NOT NULL, /*父项物料ID*/
FMATERIALID INT NOT NULL,/*子项物料ID*/
FSUPPLIERID INT NULL,
FSUPPLIERNO VARCHAR(30) NULL,
FSUPPLIERNAME VARCHAR(100) NULL,
FCYFORPRICE DECIMAL(18,6) NULL,
FTAXCYFORPRICE DECIMAL(18,6) NULL,
FPRICE DECIMAL(18,6) NULL,
FTAXPRICE DECIMAL(18,6) NULL,
FTAXAMOUNT DECIMAL(18,6) NULL,
FCYFORTYPE VARCHAR(10) NULL
)
;WITH cte AS
(
SELECT 1 AS BOM层次,h.FID 顶阶ID
,h.FID 本阶ID
,CAST(10000+b.FREPLACEGROUP AS NVARCHAR) AS 项次组合
,CAST(CAST(h.FID AS NVARCHAR(10)) +'.'+CAST(h.FID AS NVARCHAR) AS NVARCHAR(MAX)) AS 内码组合
,CAST('.'+CAST(h.FID AS VARCHAR)+'-'+CAST(10000+b.FREPLACEGROUP AS NVARCHAR) AS NVARCHAR(MAX)) AS 内码项次1
,CAST(CAST(h.FID AS NVARCHAR(10)) +'.'+CAST(h.FID AS NVARCHAR)+'-'+CAST(10000+b.FREPLACEGROUP AS NVARCHAR) AS NVARCHAR(MAX)) AS 内码_项次组合
,h.FNUMBER 产成品BOM版本
,h.FNUMBER BOM版本, h.FUSEORGID 使用组织
/*,h.FMASTERID,*/
,h.FMATERIALID PFMATERIALID,h.FQTY 批量
,b.FSEQ 子项序号,b.FREPLACEGROUP 项次
,b.FMATERIALID
,b.FMATERIALTYPE AS 子项物料类型
/*,CAST(CASE WHEN b.FMATERIALTYPE = '1' THEN '标准件'
WHEN FMATERIALTYPE ='2' THEN '返还件'
WHEN FMATERIALTYPE = '3' THEN '替代件'
ELSE '未知类型' END AS NVARCHAR(10)) AS 子项物料类型,b.FDOSAGETYPE 用量类型,*/
,b.FBASENUMERATOR 分子,b.FBASEDENOMINATOR 分母,b.FBASEFIXSCRAPQTY 固定损耗,FSCRAPRATE 变动损耗率
-- ,b.FBASENUMERATOR/b.FBASEDENOMINATOR 用量1
,CONVERT(DECIMAL(23,10),b.FBASENUMERATOR/(CASE b.FBASEDENOMINATOR WHEN 0 THEN 1 ELSE b.FBASEDENOMINATOR END )*(1+b.FBASEFIXSCRAPQTY)) 用量/*CONVERT(DECIMAL(23,10),p.用量*b.FBASENUMERATOR/b.FBASENUMERATOR*(1+b.FBASEFIXSCRAPQTY)) 用量*/
,b.FEFFECTDATE 生效日期,b.FEXPIREDATE 失效日期,b.FISSUETYPE 发料方式,b.FOWNERID 货主,FPOSITIONNO 位置号,/*b.FENTRYROWID 子项标识,*/
b.FCHILDSUPPLYORGID 供应组织,RIGHT('0000'+b.F_BHR_PLMSEQ,4) PLM序号,b.F_BHR_ECNNO ECN编号,
ba.FISSKIP 跳层,ba.FISKEYITEM 替代主料,ba.FREPLACETYPE 替代方式,ba.FREPLACEPOLICY 替代策略,ba.FREPLACEPRIORITY 替代优先级/*,b.FQTY 标准用量,b.FACTUALQTY 实际用量*/
FROM T_ENG_BOMCHILD b WITH(NOLOCK)
JOIN dbo.T_ENG_BOM h WITH(NOLOCK) ON h.FID = b.FID
JOIN dbo.T_ENG_BOMCHILD_A ba WITH(NOLOCK) ON ba.FENTRYID = b.FENTRYID
--JOIN dbo.T_ENG_BOMCHILD_A b ON h.FID=b.FID
WHERE 1=1 AND h.FDOCUMENTSTATUS='C' AND h.FFORBIDSTATUS = 'A' /*只取未禁用状态的BOM*/
AND h.FUSEORGID=100436
AND h.FNUMBER=@PFVERSION
UNION ALL
SELECT p.BOM层次+1 AS BOM层次,p.顶阶ID
,h.FID 本阶ID
,CAST(p.项次组合+'.'+CAST(10000+b.FREPLACEGROUP AS NVARCHAR) AS NVARCHAR) AS 项次组合
,CAST(p.内码组合 +'.'+CAST(h.FID AS NVARCHAR) AS NVARCHAR(MAX)) AS 内码组合
,CAST('.'+CAST(h.FID AS VARCHAR)+'-'+CAST(10000+b.FREPLACEGROUP AS NVARCHAR) AS NVARCHAR(MAX)) AS 内码项次
,CAST(p.内码_项次组合 +'.'+CAST(h.FID AS NVARCHAR)+'-'+CAST(10000+b.FREPLACEGROUP AS NVARCHAR) AS NVARCHAR(MAX)) AS 内码_项次组合
,p.产成品BOM版本
,h.FNUMBER BOM版本, h.FUSEORGID 使用组织
/*,h.FMASTERID,*/
,h.FMATERIALID PFMATERIALID,h.FQTY 批量
,b.FSEQ 子项序号,b.FREPLACEGROUP 项次
,b.FMATERIALID
,b.FMATERIALTYPE AS 子项物料类型
/*,CAST(CASE WHEN b.FMATERIALTYPE = '1' THEN '标准件'
WHEN FMATERIALTYPE ='2' THEN '返还件'
WHEN FMATERIALTYPE = '3' THEN '替代件'
ELSE '未知类型' END AS NVARCHAR(10)) AS 子项物料类型,b.FDOSAGETYPE 用量类型,*/
,b.FBASENUMERATOR 分子,b.FBASEDENOMINATOR 分母,b.FBASEFIXSCRAPQTY 固定损耗,FSCRAPRATE 变动损耗率
-- ,b.FBASENUMERATOR/b.FBASEDENOMINATOR 用量1
/*,CASE b.FMATERIALTYPE WHEN 1 THEN CONVERT(DECIMAL(23,10),p.用量*b.FBASENUMERATOR/(CASE b.FBASEDENOMINATOR WHEN 0 THEN 1 ELSE b.FBASEDENOMINATOR END )*(1+b.FBASEFIXSCRAPQTY))
ELSE 0 END
用量*/
,CONVERT(DECIMAL(23,10),p.用量*b.FBASENUMERATOR/(CASE b.FBASEDENOMINATOR WHEN 0 THEN 1 ELSE b.FBASEDENOMINATOR END )*(1+b.FBASEFIXSCRAPQTY)) 用量
--CONVERT(DECIMAL(23,10),p.用量*b.FBASENUMERATOR/b.FBASENUMERATOR*(1+b.FBASEFIXSCRAPQTY)) 用量
,b.FEFFECTDATE 生效日期,b.FEXPIREDATE 失效日期,b.FISSUETYPE 发料方式,b.FOWNERID 货主,FPOSITIONNO 位置号/*,b.FENTRYROWID 子项标识,*/
,b.FCHILDSUPPLYORGID 供应组织,RIGHT('0000'+b.F_BHR_PLMSEQ,4) PLM序号,b.F_BHR_ECNNO ECN编号
,ba.FISSKIP 跳层,ba.FISKEYITEM 替代主料,ba.FREPLACETYPE 替代方式,ba.FREPLACEPOLICY 替代策略,ba.FREPLACEPRIORITY 替代优先级/*,b.FQTY 标准用量,b.FACTUALQTY 实际用量*/
FROM cte p
JOIN dbo.T_ENG_BOM h WITH(NOLOCK)
ON h.FMATERIALID = p.FMATERIALID
JOIN #MAXFID u ON u.FID = h.FID
JOIN T_ENG_BOMCHILD b WITH(NOLOCK)
ON u.FID = b.FID --AND EXISTS(SELECT 1 FROM @p3 u WHERE u.FID=b.FID)
JOIN dbo.T_ENG_BOMCHILD_A ba WITH(NOLOCK) ON ba.FENTRYID = b.FENTRYID
WHERE 1=1 AND h.FUSEORGID=100436
)
INSERT #T_ENG_BOMITEM
SELECT --b.内码组合,b.顶阶ID,b.PFMATERIALID,
项次组合
,产成品BOM版本,BOM层次
--,顶阶ID,本阶ID,项次组合,内码组合,内码项次1,内码_项次组合
,BOM版本,使用组织,fxwl.FNUMBER 父项物料编码, 批量,子项序号,项次,
zxwl.FNUMBER 子项物料编码,zxwl.FOLDNUMBER 旧物料编码,zxwl_L.FNAME 物料名称,zxwl_L.FSPECIFICATION 规格型号,META.枚举项名称 物料属性,
子项物料类型,分子,分母,固定损耗,变动损耗率,用量,生效日期,失效日期,
发料方式,货主,位置号,供应组织,PLM序号,ECN编号,跳层,替代主料,替代方式,替代策略,替代优先级
,@FPPMATERIALID
,b.PFMATERIALID
,ISNULL(b.FMATERIALID,0)
,ISNULL(p.FSUPPLIERID,0)
,ISNULL(p.FSUPPLIERNO,'')
,ISNULL(p.FSUPPLIERNAME,'')
,ISNULL(p.FPRICE,0) FCYFORPRICE
,ISNULL(p.FTAXPRICE,0) FTAXCYFORPRICE
,ISNULL(p.FRATEPRICE,0)
,ISNULL(p.FTAXRATEPRICE,0)
,CASE b.子项物料类型 WHEN 1 THEN ISNULL(p.FTAXRATEPRICE,0)*b.用量 ELSE 0 END FTAXAMOUNT
,ISNULL(P.FCYFORTYPE,'')
FROM cte b
JOIN T_BD_MATERIAL fxwl --父项关联物料表
ON fxwl.FMATERIALID = b.PFMATERIALID
JOIN T_BD_MATERIAL_L fxwl_L --父项关联物料多语言表
ON fxwl.FMATERIALID = fxwl_l.FMATERIALID AND fxwl_L.FLOCALEID =2052
JOIN T_BD_MATERIAL zxwl --子项关联物料表
ON zxwl.FMATERIALID = b.FMATERIALID
JOIN T_BD_MATERIAL_L zxwl_L --子项关联物料多语言表
ON zxwl.FMATERIALID = zxwl_L.FMATERIALID AND zxwl_L.FLOCALEID =2052
JOIN dbo.T_BD_MATERIALBASE wlb ON wlb.FMATERIALID = b.FMATERIALID
LEFT JOIN (SELECT 枚举项名称,FVALUE
FROM [dbo].[Vw_META_FORMENUMITEM]
WHERE 枚举类型名称= 'BD_物料属性') META ON wlb.FERPCLSID=META.FVALUE
LEFT JOIN dbo.Vw_PUR_LATESTPRICELISTENTRY_DEFSUP p ON p.FMATERIALID=b.FMATERIALID
ORDER BY b.项次组合
IF(@ISREPLACE='N')
BEGIN
/*
SELECT FITEMS,FLEVEL, RIGHT('0000'+CONVERT( VARCHAR(4),FSEQ),4) FSEQ,RIGHT('0000'+CONVERT( VARCHAR(4),FREPLACEGROUP),4) FREPLACEGROUP
,CASE FMATERIALTYPE WHEN 3 THEN '3:替代件' WHEN 2 THEN '2:返还件' WHEN 1 THEN '1:标准件' ELSE '其他类型' END FMATERIALTYPE
,FPPMATERIALID,FPMATERIALID,FMATERIALID
,FPMATERIALNO,FMATERIALNO,FMATERIALNAME,FSPECIFICATION,FERPCLSID,FCYFORTYPE
,FNUMERATOR,FDENOMINATOR,FQTY,FPOSITIONNO,FSUPPLIERID,FSUPPLIERNO, FSUPPLIERNAME, FPRICE
, FTAXPRICE,FCYFORPRICE,FTAXCYFORPRICE, FTAXAMOUNT FROM #T_ENG_BOMITEM*/
INSERT INTO dbo.BHR_GET_T_ENG_BOMITEM
(
FGUID, 项次组合,产成品BOM版本,BOM层次, BOM版本, 使用组织ID, 父项物料编码,批量,子项序号,项次,子项物料编码,旧物料编码, 物料名称,规格型号,
物料属性,子项物料类型,分子,分母,固定损耗, 变动损耗率,用量, 生效日期, 失效日期,发料方式,货主,位置号,供应组织,PLM序号, ECN编号,跳层,替代主料,替代方式,替代策略,替代优先级
)
SELECT @GUID,FITEMS,FTOPVERSION,
FLEVEL,
FVERSION,
FUSEORGID,
FPMATERIALNO,
FLOT,
FSEQ,
FREPLACEGROUP,
FMATERIALNO,
FOLDMATERIALNO,
FMATERIALNAME,
FSPECIFICATION,
FERPCLSID,
FMATERIALTYPE,
FNUMERATOR,
FDENOMINATOR,
FFIXSCRAPQTY,
FSCRAPRATE,
FQTY*@SOSNUMBER,
FEFFECTDATE,
FEXPIREDATE,
FISSUETYPE,
FOWNERID,
FPOSITIONNO,
FCHILDSUPPLYORGID,
F_BHR_PLMSEQ,
F_BHR_ECNNO,
FISSKIP,
FISKEYITEM,
FREPLACETYPE,
FREPLACEPOLICY,
FREPLACEPRIORITY FROM #T_ENG_BOMITEM WHERE FMATERIALTYPE=1
END
ELSE
BEGIN
INSERT INTO dbo.BHR_GET_T_ENG_BOMITEM
(
FGUID, 项次组合,产成品BOM版本,BOM层次, BOM版本, 使用组织ID, 父项物料编码,批量,子项序号,项次,子项物料编码,旧物料编码, 物料名称,规格型号,
物料属性,子项物料类型,分子,分母,固定损耗, 变动损耗率,用量, 生效日期, 失效日期,发料方式,货主,位置号,供应组织,PLM序号, ECN编号,跳层,替代主料,替代方式,替代策略,替代优先级
)SELECT @GUID, FITEMS,FTOPVERSION,
FLEVEL,
FVERSION,
FUSEORGID,
FPMATERIALNO,
FLOT,
FSEQ,
FREPLACEGROUP,
FMATERIALNO,
FOLDMATERIALNO,
FMATERIALNAME,
FSPECIFICATION,
FERPCLSID,
FMATERIALTYPE,
FNUMERATOR,
FDENOMINATOR,
FFIXSCRAPQTY,
FSCRAPRATE,
FQTY*@SOSNUMBER,
FEFFECTDATE,
FEXPIREDATE,
FISSUETYPE,
FOWNERID,
FPOSITIONNO,
FCHILDSUPPLYORGID,
F_BHR_PLMSEQ,
F_BHR_ECNNO,
FISSKIP,
FISKEYITEM,
FREPLACETYPE,
FREPLACEPOLICY,
FREPLACEPRIORITY FROM #T_ENG_BOMITEM
/*SELECT @GUID, 项次组合, 产成品BOM版本,BOM层次, BOM版本,使用组织ID,父项物料编码, 批量,子项序号,项次,子项物料编码,旧物料编码,物料名称,规格型号,
物料属性,子项物料类型,分子,分母, 固定损耗, 变动损耗率, CASE 子项物料类型 WHEN 3 THEN 用量*0 ELSE 用量*@SOSNUMBER END,生效日期,失效日期,发料方式,货主,位置号,供应组织,PLM序号, ECN编号,跳层,替代主料,替代方式,替代策略,替代优先级
FROM [dbo].[Get_T_ENG_BOMITEM] (@PFNUMBER)*/
END
UPDATE dbo.BHR_GET_T_ENG_BOMENTRY SET FSTATUS='2' WHERE @GUID=@GUID AND FMATERIALNO=@PFNUMBER
DELETE FROM #Q_BOM WHERE FMATERIALNO=@PFNUMBER AND FGUID=@GUID
SET @r=@r-1;
END
UPDATE dbo.BHR_GET_T_ENG_BOM SET FSTATUS='2' WHERE FGUID=@GUID
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
UPDATE dbo.BHR_GET_T_ENG_BOM SET FSTATUS='3',FRELOG=@PFNUMBER+':'+ERROR_MESSAGE() WHERE FGUID=@GUID;
IF (XACT_STATE())=-1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +' Rolling back transaction.'
ROLLBACK TRANSACTION
PRINT '发生错误'
/*EXEC (' DROP TABLE '+@TabName)*/
END
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION;
END;
END CATCH
END
云星空 Cloud 利用递归 自定义批量查询BOM查尾阶 替代料用量取0 金额取0
最新推荐文章于 2023-09-05 09:34:46 发布