Epicor Indented BOM Recursive CTE SQL Generator (PartMtl, PartRev)

WITH ChildRev AS (
    -- one approved revision per part, primary method, latest effective date wins
    SELECT pr.Company, pr.PartNum, pr.RevisionNum, pr.AltMethod,
           ROW_NUMBER() OVER (PARTITION BY pr.Company, pr.PartNum
                              ORDER BY pr.EffectiveDate DESC, pr.RevisionNum DESC) AS rn
    FROM Erp.PartRev pr
    WHERE pr.Company = 'EPIC06'
      AND pr.Approved = 1
      AND pr.AltMethod = ''
),
BOM (Lvl, ParentPart, ParentRev, MtlSeq, MtlPartNum, QtyPer, ExtQty, SortPath) AS (
    -- anchor: materials of the top part
    SELECT 1,
           pm.PartNum,
           pm.RevisionNum,
           pm.MtlSeq,
           pm.MtlPartNum,
           pm.QtyPer,
           CAST(pm.QtyPer AS decimal(22,8)),
           CAST(RIGHT('0000' + CAST(pm.MtlSeq AS varchar(10)), 4) AS varchar(4000))
    FROM Erp.PartRev pr
    JOIN Erp.PartMtl pm
      ON pm.Company = pr.Company
     AND pm.PartNum = pr.PartNum
     AND pm.RevisionNum = pr.RevisionNum
     AND pm.AltMethod = pr.AltMethod
    WHERE pr.Company = 'EPIC06'
      AND pr.PartNum = 'FG-1000'
      AND pr.AltMethod = '[Alternate method of the top part (blank = primary)]'
      AND (pr.RevisionNum = 'A' OR ('A' = '' AND pr.Approved = 1))

    UNION ALL

    -- recursive member: materials of each child that has its own method
    SELECT b.Lvl + 1,
           pm.PartNum,
           pm.RevisionNum,
           pm.MtlSeq,
           pm.MtlPartNum,
           pm.QtyPer,
           CAST(b.ExtQty * pm.QtyPer AS decimal(22,8)),
           CAST(b.SortPath + '.' + RIGHT('0000' + CAST(pm.MtlSeq AS varchar(10)), 4) AS varchar(4000))
    FROM BOM b
    JOIN ChildRev cr
      ON cr.PartNum = b.MtlPartNum
     AND cr.rn = 1
    JOIN Erp.PartMtl pm
      ON pm.Company = cr.Company
     AND pm.PartNum = cr.PartNum
     AND pm.RevisionNum = cr.RevisionNum
     AND pm.AltMethod = cr.AltMethod
    WHERE b.Lvl < 10
)
SELECT REPLICATE('  ', b.Lvl - 1) + b.MtlPartNum AS IndentedPart,
       b.Lvl, b.ParentPart, b.ParentRev, b.MtlSeq, b.MtlPartNum,
       p.PartDescription, b.QtyPer, b.ExtQty, b.SortPath
FROM BOM b
JOIN Erp.Part p
  ON p.Company = 'EPIC06'
 AND p.PartNum = b.MtlPartNum
ORDER BY b.SortPath
OPTION (MAXRECURSION 100);

The BAQ Designer does not accept pasted SQL. Rebuild it as subqueries:

1. SubQuery1, type CTE: tables PartRev and PartMtl joined on Company, PartNum, RevisionNum, AltMethod. Criteria PartRev.PartNum = 'FG-1000', AltMethod = '[Alternate method of the top part (blank = primary)]', revision filter. Display PartMtl.PartNum, RevisionNum, MtlSeq, MtlPartNum, QtyPer and calculated fields Lvl (int) = 1, ExtQty (decimal) = PartMtl.QtyPer.

2. SubQuery2, type UnionAll: the SubQuery1 CTE joined to PartRev (Approved = true, AltMethod blank) on PartRev.PartNum = SubQuery1.PartMtl_MtlPartNum, then PartMtl joined to PartRev. Same display columns in the same order; Lvl = SubQuery1.Calculated_Lvl + 1, ExtQty = SubQuery1.Calculated_ExtQty * PartMtl.QtyPer; criteria SubQuery1.Calculated_Lvl < 10.

3. SubQuery3, type TopLevel: select from SubQuery1, join Part for PartDescription, sort as needed.

The ROW_NUMBER revision filter has no direct BAQ equivalent inside the recursive member; if children carry several approved revisions, keep one approved revision per part as a business rule or filter on a UD flag.