Kingdee 云星空 7.7 自定义BOM成本查询替代料用量取0 金额取0

蜗牛～turbo
已于 2022-04-14 19:45:17 修改
阅读量748
点赞数 1
分类专栏： Kingdee ERP 文章标签： sql sqlserver
于 2022-04-14 19:42:31 首次发布
本文链接：https://blog.csdn.net/weixin_41120428/article/details/124179833
版权
Kingdee ERP 专栏收录该内容
16 篇文章 5 订阅
订阅专栏
Kingdee 云星空 7.7 自定义BOM成本查询替代料用量取0 金额取0

-- =============================================
-- Author:		自定义BOM成本查询 替代料用量取0 金额取0
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Proc_T_ENG_BOMITEM_SUPPLIERPRICEDEFFAULT]
	@PFNUMBER NVARCHAR(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

--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 @PFNUMBER=MAX(FNUMBER),@FPPMATERIALID=MAX(FMATERIALID) FROM dbo.T_ENG_BOM WHERE FNUMBER LIKE @PFNUMBER+'%'AND FFORBIDSTATUS='A' AND FDOCUMENTSTATUS='C'AND FUSEORGID=100436

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=@PFNUMBER

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
,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.枚举项名称 物料属性,
 子项物料类型,分子,分母,固定损耗,变动损耗率
 ,(CASE b.子项物料类型 WHEN 1 THEN b.用量 ELSE 0 END) AS 用量
 ,生效日期,失效日期,
       发料方式,货主,位置号,供应组织,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) AS 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.项次组合 
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

END