USE [AIS202005010001]
GO
/****** Object: StoredProcedure [dbo].[P_GET_ICBOMCHILD] Script Date: 2022/5/18 12:16:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <LOUISPENG>
-- Create date: <20220514>
-- Description: <GETICBOM ICBOM>
-- =============================================
ALTER PROCEDURE [dbo].[P_GET_ICBOMCHILD]
-- Add the parameters for the stored procedure here
@FFNUMBER VARCHAR(50) --产品编码
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT B.FEntryID 分录号,c.FNumber '父项物料代码',c.FName '父项物料名称',STR(a.FQty) '父项数量',d.FNumber '子项物料代码',d.FName '子项物料名称',D.FModel 子项规格,
--(CASE WHEN D.FErpClsID=1 THEN '外购' WHEN D.FErpClsID=2 THEN '自制' WHEN D.FErpClsID=3 THEN '委外件' ELSE '虚拟件' END) 物料属性,
--STR(b.FQty) '子件单位用量',CONVERT(VARCHAR(20),a.FcheckDate,112) '建立时间',CONVERT(VARCHAR(20), A.FAudDate,112) 审核日期
--from ICBom a
--inner join ICBomChild b
--ON a.FinterID=b.FinterID
--left JOIN T_ICItem c
--ON a.FItemID=c.FItemID
--left join T_ICItem d
--ON b.FItemID=d.FItemID
--WHERE C.FNumber=@FFNUMBER --AND C.FErpClsID IN (2,3,5)
--ORDER BY a.FBOMNumber
WITH CTE_ICBOM AS(
SELECT B.FEntryID 分录号,c.FNumber '父项物料代码',c.FName '父项物料名称',CONVERT(numeric(10,3), a.FQty) '父项数量',d.FNumber '子项物料代码',d.FName '子项物料名称',D.FModel 子项规格,
(CASE WHEN D.FErpClsID=1 THEN '外购' WHEN D.FErpClsID=2 THEN '自制' WHEN D.FErpClsID=3 THEN '委外件' ELSE '虚拟件' END) 物料属性,
(CONVERT(numeric(10,3),b.FQty)) '子件单位用量',CONVERT(VARCHAR(20),a.FcheckDate,112) '建立时间',CONVERT(VARCHAR(20), A.FAudDate,112) 审核日期
from ICBom a
inner join ICBomChild b
ON a.FinterID=b.FinterID
INNER JOIN T_ICItem c
ON a.FItemID=c.FItemID
INNER join T_ICItem d
ON b.FItemID=d.FItemID
WHERE C.FNumber IN ( @FFNUMBER )
UNION ALL
SELECT B.FEntryID 分录号,c.FNumber '父项物料代码',c.FName '父项物料名称',CONVERT(numeric(10,3),a.FQty) '父项数量',d.FNumber '子项物料代码',d.FName '子项物料名称',D.FModel 子项规格,
(CASE WHEN D.FErpClsID=1 THEN '外购' WHEN D.FErpClsID=2 THEN '自制' WHEN D.FErpClsID=3 THEN '委外件' ELSE '虚拟件' END) 物料属性,
(CONVERT(NUMERIC(10,3), b.FQty)) '子件单位用量',CONVERT(VARCHAR(20),a.FcheckDate,112) '建立时间',CONVERT(VARCHAR(20), A.FAudDate,112) 审核日期
from ICBom a
inner join ICBomChild b
ON a.FinterID=b.FinterID
INNER JOIN T_ICItem c
ON a.FItemID=c.FItemID
INNER join T_ICItem d
ON b.FItemID=d.FItemID
INNER JOIN CTE_ICBOM e ON C.FNumber=E.子项物料代码 --建立递归调用从定位点查找父代码与子代码匹配物料
WHERE C.FNumber IN (E.子项物料代码) --获取所有有父代码的子代码的物料
)
SELECT * FROM CTE_ICBOM ORDER BY 父项物料代码 DESC
Option(MAXRECURSION 10)
END