Hi All,
I thought I would write a post that covers a common necessity. One also needs to have filter for the occurrence of a non-zero and the current effective date.
I’ve included both the Multi-Level CTE Recursion and the Non-Zero and Effective Date view which is inner joined.
Let me know if you have questions.
Vince
This is the Multi-Level CTE
Hi All,
I thought I would write a post that covers a common necessity. One also needs to have filter for the occurrence of a non-zero and the current effective date.
I’ve included both the Multi-Level CTE Recursion and the Non-Zero and Effective Date view which is inner joined.
Let me know if you have questions.
Vince
This is the Multi-Level CTE
DECLARE @Part CHAR(30)
SET @Part = ‘11000’
;WITH BOMcte(COMPRT_02, PMDES1_01, QTYPER_02, LLC_01, PARPRT_02, EFFDTE_02, MAXID, REFDES_02, type_01, PTYPE, BOMUOM_01, pllc_01)
AS
(
SELECT
b.COMPRT_02,
CAST(p.PMDES1_01 as nvarchar(100)),
b.QTYPER_02,
p.LLC_01,
b.PARPRT_02,
b.EFFDTE_02,
b.MAXID,
b.REFDES_02,
p.TYPE_01,
pp.TYPE_01 AS PTYPE,
pp.BOMUOM_01,
PP.LLC_01
FROM
PRODUCT_STRUCTURE AS b
INNER JOIN
EffNonZero as CE
on b.PARPRT_02 = CE.PARPRT_02 and b. COMPRT_02 = CE. COMPRT_02 and b.EFFDTE_02 = CE.EFFDTE_02
— Gets all the non-zero qty relationship and the current effective date
INNER JOIN Part_Master p
on b.COMPRT_02 = p.PRTNUM_01
INNER JOIN Part_Master pp
on b.PARPRT_02 = pp.PRTNUM_01
WHERE b.PARPRT_02 = @part
UNION ALL
SELECT b.COMPRT_02,
CAST(p.PMDES1_01 as NVARCHAR(100)),
b.QTYPER_02 * cte.QTYPER_02 as QTYPER_02,
p.LLC_01,
b.PARPRT_02,
b.EFFDTE_02,
b.MAXID,
b.REFDES_02,
p.TYPE_01,
pp.TYPE_01 AS PTYPE,
pp.BOMUOM_01,
PP.LLC_01
FROM Product_Structure as b
INNER JOIN
EffNonZero as CE
on b.PARPRT_02 = CE.PARPRT_02 and b. COMPRT_02 = CE. COMPRT_02 and b.EFFDTE_02 = CE.EFFDTE_02
— Gets all the non-zero qty relationship and the current effective date
INNER JOIN Part_Master p
on b.COMPRT_02 = p.PRTNUM_01
INNER JOIN Part_Master pp
on b.PARPRT_02 = pp.PRTNUM_01
INNER JOIN BOMcte AS cte
ON b.PARPRT_02 = cte.COMPRT_02
)
SELECT * FROM BOMcte
Here’s the Non-Zero Qty and Effective Date link.
SELECT PARPRT_02, COMPRT_02, MAX(EFFDTE_02) AS EFFDTE_02
FROM dbo.Product_Structure
WHERE (EFFDTE_02 <= DATEADD(Day, DATEDIFF(Day, 1, SYSDATETIME()), 1))
GROUP BY PARPRT_02, COMPRT_02
EXCEPT
SELECT PARPRT_02, COMPRT_02, MAX(EFFDTE_02) AS EFFDTE_02
FROM dbo.Product_Structure
WHERE (EFFDTE_02 <= DATEADD(Day, DATEDIFF(Day, 1, SYSDATETIME()), 1)) AND QTYPER_02 = 0
GROUP BY PARPRT_02, COMPRT_02
[category T-SQL Code]
[tags Exact MAX, Kewill MAX, Micro-MRP,Inc., ExactMAX ERP, Exact MAX Modules, Exact MAX Training]