Recursive Query

I have a T-SQL recursive query for extracting a Bill Of Material. It works with one caveat. Each compenent in the BOM has an expiration date (when it is revised or updated) I only want to show current items (Items with no EXP date or in SQL year 1753). I managed to make this work at level 0. I simply do a where exp date = 1753 and all my current level zero items print. However, this is not working for the lower level items. I seem to keep getting all the revisions of components level 1 down. Can anyone look at my script and tell me where to filter that part out?

Thanks

with BOMCTE AS (
  SELECT t_mitm as Assy, t_sitm as Assy2, t_sitm as Comp, t_qana, 0 AS lvl, year(t_exdt) as 'YearExp', t_pono
  FROM ttibom010100
  where year(t_exdt) = 1753
  UNION ALL
 
  SELECT usr.Assy, mgr.t_mitm, mgr.t_sitm, mgr.t_qana, usr.lvl +1 AS lvl, YearExp, mgr.t_pono
  FROM BOMCTE AS usr
    INNER JOIN ttibom010100 AS mgr
      ON usr.Comp = mgr.t_mitm
        where usr.YearExp = 1753
)

SELECT *
  FROM BOMCTE AS u
  where Assy = '8730'
  ORDER BY Assy, lvl, Assy2;
jsgrosskopfIS ManagerAsked:
Who is Participating?
 
UnifiedISCommented:
I recognize that table name, Baan/LN?
You can also post-date the effective date so you should also be comparing t_indt (t_indt < GETDATE()).

In this one, you need to look at the expiration date (and effective date) on ttibom010100 (your mgr)
  SELECT usr.Assy, mgr.t_mitm, mgr.t_sitm, mgr.t_qana, usr.lvl +1 AS lvl, YearExp, mgr.t_pono
  FROM BOMCTE AS usr
    INNER JOIN ttibom010100 AS mgr
      ON usr.Comp = mgr.t_mitm
        where YEAR(mgr.t_exdt) = 1753
0
 
jsgrosskopfIS ManagerAuthor Commented:
Eureka!. I now have all the correct current components. Could you tell me if you know one last thing? (If not, I'll just mark this as answered and submit another question). I am trying to present this in SQL Server Reporting Services. I am doing a recursive group. I am using the "Comp" as the group with "Assy2" as the parent. The only issue I have is when a sub component is used multiple times on an assembly. ex:

Assy: 8730

Lvl 1: 4511-1 Qty 1
Lvl 2: A023502 Qty 2

Lvl 1: 4564 Qty 1
Lvl 2: A023502 Qty 2

Because A023502 shows up until 4511-1 first, I get:

Lvl 1: 4511-1 Qty 1
Lvl 2: A023502 Qty 4

Lvl 1: 4564 Qty 1

Which is technically correct (I do need for of them for the assembly) but I'd like it to repeat that component and show it under each level 1

Any idea?

Thanks again
0
 
UnifiedISCommented:
Here is a stored procedure to extract 10 levels from an LN BOM.  It takes either a comma separated list of items or an item range (via start item and end item).
I execute this proc from other procs and load the data to a temp table and then join back to whatever other tables I need to grab data from.  In LN, they use a future date (2038-01-18)  instead of 1753-01-01 so you would need to tweak the spot where I compare with GETDATE()

/*
2013-08-15 DBC--returns an indented bill of material for a range of items and/or a csv of items
      --to be consumed by other procs to allow customization of the supplemental information
2014-01-31 DBC--reworked the method, pulling all BOM into a temp table then looping through to add the lower levels
      --This improved processing time quite a bit
2014-07-24 DBC--removed validation for items when using the itemcsv, there is already a join to the BOM table which will validate the items
*/

CREATE PROCEDURE [dbo].[BOM_10Levels]

@ItemCSV varchar(MAX),
@Item1 varchar(50),
@Item2 varchar(50)

AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @ItemCSV varchar(MAX)
--DECLARE @Item1 varchar(50)
--DECLARE @Item2 varchar(50)
--SET @ItemCSV = '03037,03038'
--SET @Item1 = '75613'
--SET @Item2 = '79000'


DECLARE @Items Table (
      t_item varchar(50)
      )

IF ISNULL(@ItemCSV, '') <> ''
      BEGIN
            DECLARE @Item varchar(50)
            DECLARE @Position int
            SET @ItemCSV = LTRIM(RTRIM(@ItemCSV)) + ','
            SET @Position = CHARINDEX(',', @ItemCSV, 1)

            IF REPLACE(@ItemCSV, ',', '') <> ''
                  BEGIN
                        WHILE @Position > 0
                        BEGIN
                              SET @Item = space(9) + LTRIM(RTRIM(LEFT(@ItemCSV, @Position -1)))
                              IF @ItemCSV <> ''
                              BEGIN      
                                    INSERT INTO @Items (t_item)
                                    SELECT @Item
                                    --SELECT t_item
                                    --FROM ttcibd001100
                                    --WHERE LTRIM(RTRIM(t_item)) = @Item
                              END

                              SET @ItemCSV = RIGHT(@ItemCSV, LEN(@ItemCSV) - @Position)
                              SET @Position = CHARINDEX(',', @ItemCSV, 1)
                        END
                  END
      END


IF ISNULL(@Item1, '') <> ''
      BEGIN
            SET @Item1 = space(9) + UPPER(LTRIM(RTRIM(ISNULL(@Item1, ''))))
            SET @Item2 = space(9) + UPPER(LTRIM(RTRIM(ISNULL(@Item2, ''))))
                  IF LEN(LTRIM(@Item2) ) = 0
                        BEGIN
                              SET @Item2 = @Item1
                        END      
                        
            INSERT INTO @Items (t_item)
            SELECT t_item
            FROM ttcibd001100
            WHERE t_item BETWEEN @Item1 AND @Item2
      END
      
--DECLARE @Epoch datetime
--SELECT @Epoch = Epoch1970 FROM EpochDates

DECLARE @ERPBOM Table (
      t_mitm varchar(50),
      t_sitm varchar(50)
      )
INSERT INTO @ERPBOM (t_mitm, t_sitm)
SELECT t_mitm, t_sitm
FROM ttibom010100
WHERE t_indt < GETDATE()
      AND t_exdt > GETDATE()

DECLARE @BOM TAble (
      KeyItem varchar(50),
      KeyItemLevel int,
      ImmediateParent varchar(50),
      TopLevelParent varchar(50),
      Level1Child varchar(50),
      Level2Child varchar(50),
      Level3Child varchar(50),
      Level4Child varchar(50),
      Level5Child varchar(50),
      Level6Child varchar(50),
      Level7Child varchar(50),
      Level8Child varchar(50),
      Level9Child varchar(50),
      Level10Child varchar(50)
      )

INSERT INTO @BOM (KeyItem, TopLevelParent, KeyItemLevel)--, Level1Parent, Level2Parent, Level3Parent, Level4Parent, Level5Parent, Level6Parent, Level7Parent, Level8Parent, Level9Parent, Level10Parent)
SELECT t_item, t_item, 0
FROM @Items

DECLARE @MaxDepth int
DECLARE @Level int
SET @MaxDepth = 10
SET @Level = 1

--SELECT *
--FROM @BOM      
      
WHILE @Level < @MaxDepth
      BEGIN
            INSERT INTO @BOM (TopLevelParent, KeyItem, KeyItemLevel, Level1Child, Level2Child, Level3Child, Level4Child, Level5Child, Level6Child, Level7Child, Level8Child, Level9Child, Level10Child)
            SELECT B.TopLevelParent,
                  E.t_sitm,
                  @Level,
                  Level1Child = CASE WHEN @Level = 1 THEN E.t_sitm
                                                ELSE B.Level1Child
                                          END,
                  Level2Child = CASE WHEN @Level = 2 THEN E.t_sitm
                                                ELSE B.Level2Child
                                          END,
                  Level3Child = CASE WHEN @Level = 3 THEN E.t_sitm
                                                ELSE B.Level3Child
                                          END,
                  Level4Child = CASE WHEN @Level = 4 THEN E.t_sitm
                                                ELSE B.Level4Child
                                          END,
                  Level5Child = CASE WHEN @Level = 5 THEN E.t_sitm
                                                ELSE B.Level5Child
                                          END,
                  Level6Child = CASE WHEN @Level = 6 THEN E.t_sitm
                                                ELSE B.Level6Child
                                          END,
                  Level7Child = CASE WHEN @Level = 7 THEN E.t_sitm
                                                ELSE B.Level7Child
                                          END,
                  Level8Child = CASE WHEN @Level = 8 THEN E.t_sitm
                                                ELSE B.Level8Child
                                          END,
                  Level9Child = CASE WHEN @Level = 9 THEN E.t_sitm
                                                ELSE B.Level9Child
                                          END,
                  Level10Child = CASE WHEN @Level = 10 THEN E.t_sitm
                                                ELSE B.Level10Child
                                          END                                                
            FROM @BOM B
            INNER JOIN @ERPBOM E
                  ON B.KeyItemLevel = @Level - 1
                  AND B.KeyItem = E.t_mitm
      
            SET @Level = @Level + 1
      
            IF @Level < @MaxDepth
                  CONTINUE
            ELSE
                  BREAK
      
      END


----set the immediate parent column, this will allow linking back to the bom for additional information
UPDATE @BOM
SET ImmediateParent = CASE WHEN Level10Child IS NOT NULL THEN Level9Child
                              WHEN Level9Child IS NOT NULL THEN Level8Child
                              WHEN Level8Child IS NOT NULL THEN Level7Child
                              WHEN Level7Child IS NOT NULL THEN Level6Child
                              WHEN Level6Child IS NOT NULL THEN Level5Child
                              WHEN Level5Child IS NOT NULL THEN Level4Child
                              WHEN Level4Child IS NOT NULL THEN Level3Child
                              WHEN Level3Child IS NOT NULL THEN Level2Child
                              WHEN Level2Child IS NOT NULL THEN Level1Child
                              WHEN Level1Child IS NOT NULL THEN TopLevelParent
                              ELSE NULL
                        END

SELECT *
FROM @BOM
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
UnifiedISCommented:
Here is one of the procedures I use that calls the BOM_10Levels procedure.  It grabs sales/purchase/production order information for each component in the BOM.  I use Crystal Reports for presentation and don't have much experience with SSRS but packaging the data in this manner might make it easier for you to lay it out on the report.

/*
2013-08-16 DBC--returns an indented BOM for each item input along with supporting item and order information
2014-08-01 DBC--added project manual order type
*/
CREATE PROCEDURE [dbo].[RPT_BOM_WithOrderInformation]

@ItemCSV varchar(MAX),
@Flavor varchar(50),
@Item1 varchar(50),
@Item2 varchar(50)

AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @ItemCSV varchar(MAX)
--DECLARE @Item1 varchar(50)
--DECLARE @Item2 varchar(50)
--DECLARE @Flavor varchar(50)

DECLARE @Flavor_PTPro varchar(50)
SET @Flavor_PTPro = 'PT PRO'
DECLARE @Flavor_MRP_WithPurchasedComponents varchar(50)
SET @Flavor_MRP_WithPurchasedComponents = 'MRP WITH PURCHASED COMPONENTS'
SET @Flavor = ISNULL(@Flavor, '')--@Flavor_MRP_WithPurchasedComponents

--SET @Flavor = @Flavor_PTPro
--SET @Item1 = '79256'
DECLARE @OrderType_Purchase int
DECLARE @OrderType_Sales int
DECLARE @OrderType_Production int
DECLARE @OrderType_ProjectManual int
SET @OrderType_Purchase = 2
SET @OrderType_Sales = 3
SET @OrderType_Production = 1
SET @OrderType_ProjectManual = 59

DECLARE @Issue int
DECLARE @Receipt int
SET @Issue = 2
SET @Receipt = 1

DECLARE @Epoch datetime
SELECT @Epoch = Epoch1970 FROM EpochDates

DECLARE @ItemCSV_FromFlavor varchar(MAX)      
IF ISNULL(@Flavor, '') <> ''
      BEGIN
            IF @Flavor = @Flavor_PTPro
                  BEGIN
                        SELECT @ItemCSV_FromFlavor = ISNULL(@ItemCSV_FromFlavor + ',', '') + LTRIM(RTRIM(t_item))
                        FROM ttcibd001100
                        WHERE LTRIM(RTRIM(t_item)) BETWEEN '79252' AND '79260'
                        
                        --SELECT @ItemCSV_FromFlavor
                  END      
            IF @Flavor = @Flavor_MRP_WithPurchasedComponents
                  BEGIN
                        SELECT @ItemCSV_FromFlavor = ISNULL(@ItemCSV_FromFlavor + ',', '') + LTRIM(RTRIM(IBD.t_item))
                        FROM ttcibd001100 IBD
                        INNER JOIN ttcibd200100 IBD200
                              ON IBD.t_item = IBD200.t_item
                        INNER JOIN GR_FinancialWarehouses WH
                              ON WH.t_cwar = IBD200.t_cwar
                        INNER JOIN vw_GRProductHierarchy GRPH
                              ON GRPH.ProductClass = IBD.t_cpcl
                              AND GRPH.GRGR500_ID NOT IN (0, 1, 4, 19, 2,13)
                        LEFT OUTER JOIN ttibom010100 BOM
                              ON BOM.t_sitm = IBD.t_item
                              AND BOM.t_indt < GETDATE()
                              AND BOM.t_exdt > GETDATE()
                        WHERE IBD200.t_osys = 2 --planned
                              AND IBD.t_csig <> '004'
                              AND IBD.t_kitm = 2 --manufactured
                              AND BOM.t_mitm IS NULL--item is never a child in a BOM, i.e. a top level parent item

                        
                        --SELECT @ItemCSV_FromFlavor
                  END      
      END

DECLARE @BOM Table(
      KeyItem varchar(50),
      KeyItemLevel int,
      ImmediateParent varchar(50),
      TopLevelParent varchar(50),
      Level1Child varchar(50),
      Level2Child varchar(50),
      Level3Child varchar(50),
      Level4Child varchar(50),
      Level5Child varchar(50),
      Level6Child varchar(50),
      Level7Child varchar(50),
      Level8Child varchar(50),
      Level9Child varchar(50),
      Level10Child varchar(50)
      )
      
SELECT @ItemCSV = ISNULL(@ItemCSV + ',', '') + ISNULL(@ItemCSV_FromFlavor, '')
--SELECT @ItemCSV
      
INSERT INTO @BOM
EXECUTE BOM_10Levels
      @ItemCSV,
      @Item1,
      @Item2


DELETE FROM @BOM
WHERE KeyItemLevel > 2
      AND @Flavor = @Flavor_MRP_WithPurchasedComponents
      

SELECT DISTINCT      LTRIM(RTRIM(BOM.KeyItem)) AS KeyItem,
      BOM.KeyItemLevel,
      IBD.t_dsca AS KeyItemDescription,
      LTRIM(RTRIM(BOM.ImmediateParent)) AS ImmediateParent,
      LTRIM(RTRIM(BOM.TopLevelParent)) AS TopLevelParent,
      LTRIM(RTRIM(BOM.Level1Child)) AS Level1Child,
      LTRIM(RTRIM(BOM.Level2Child)) AS Level2Child,
      LTRIM(RTRIM(BOM.Level3Child)) AS Level3Child,
      LTRIM(RTRIM(BOM.Level4Child)) AS Level4Child,
      LTRIM(RTRIM(BOM.Level5Child)) AS Level5Child,
      LTRIM(RTRIM(BOM.Level6Child)) AS Level6Child,
      LTRIM(RTRIM(BOM.Level7Child)) AS Level7Child,
      LTRIM(RTRIM(BOM.Level8Child)) AS Level8Child,
      LTRIM(RTRIM(BOM.Level9Child)) AS Level9Child,
      LTRIM(RTRIM(BOM.Level10Child)) AS Level10Child,
      ItemType = CASE IBD.t_kitm WHEN 1 THEN 'Purchased'
                        WHEN 2 THEN 'Manufactured'
                        WHEN 4 THEN 'Cost'
                        WHEN 6 THEN 'Subcontracting'
                        ELSE 'Unknown'
                  END,
      CPR007.t_ecpr_1 AS StdCost,
      IBD100.t_stoc AS OnHand,
      CPR007.t_ecpr_1 * IBD100.t_stoc AS ExtendedValue,
      IBD100.t_allo AS Allocated,
      IBD100.t_ordr AS OnOrder,
      IBD200.t_cwar AS Warehouse,
      IBD200.t_oqmf AS OrderQtyMultiple,
      IBD200.t_mioq AS MinimumOrderQty,
      IBD200.t_sfst AS SafetyStock,
      IBD200.t_reop AS ReorderPoint,
      IBD200.t_cplb AS Planner,
      ORD.OrderType,
      ORD.TransactionType,
      ORD.t_orno AS OrderNo,
      ORD.t_qana AS OrderQty,
      ORD.t_date AS OrderDueDate,
      ORD.CurrentOp,
      ORD.OpDueDate,
      ORD.CurrentOpWC,
      BOMDET.t_qana AS BOMQty,
      BOMDET.t_cwar as BOMWarehouse
FROM @BOM BOM
INNER JOIN ttcibd001100 IBD
      ON IBD.t_item = BOM.KeyItem
      AND IBD.t_csig <> '004'
INNER JOIN ttcibd200100 IBD200
      ON IBD200.t_item = IBD.t_item      
LEFT OUTER JOIN tticpr007100 CPR007
      ON CPR007.t_item = BOM.KeyItem
LEFT OUTER JOIN ttcibd100100 IBD100
      ON IBD100.t_item = BOM.KeyItem
LEFT OUTER JOIN ttibom010100 BOMDET
      ON BOMDET.t_sitm = BOM.KeyItem
      AND BOMDET.t_mitm = BOM.ImmediateParent
      AND BOMDET.t_indt < GETDATE()
      AND (BOMDET.t_exdt > GETDATE() OR BOMDET.t_exdt = @Epoch)      
LEFT OUTER JOIN (      
      SELECT WHINP.t_item,
            WHINP.t_orno,
            WHINP.t_date,
            OrderType = CASE WHINP.t_koor WHEN @OrderType_Production THEN 'Production Order'
                                                            WHEN @OrderType_Purchase THEN 'Purchase Order'
                                                            WHEN @OrderType_Sales THEN 'Sales Order'
                                                            WHEN @OrderType_ProjectManual THEN 'Project (Manual)'
                                                            ELSE NULL
                              END,
            TransactionType = CASE WHINP.t_kotr WHEN @Issue THEN 'Issue'
                                                                  WHEN @Receipt THEN 'Receipt'
                                                                  ELSE NULL
                              END,
            WHINP.t_qana,
            WHINP.t_cwar,
            OPS.MinOpenOp AS CurrentOp,
            SFC.t_fidt AS OpDueDate,
            SFC.t_cwoc AS CurrentOpWC
      FROM twhinp100100 WHINP
      INNER JOIN @BOM B
            ON B.KeyItem = WHINP.t_item

      LEFT OUTER JOIN (SELECT MIN(t_opno) AS MinOpenOp,
                                    t_pdno,
                                    t_item
                              FROM ttisfc010100
                              WHERE t_opst < 7
                              GROUP BY t_pdno, t_item) OPS
            ON OPS.t_pdno = WHINP.t_orno
            AND WHINP.t_koor = @OrderType_Production
            AND OPS.t_item = WHINP.t_item
      LEFT OUTER JOIN ttisfc010100 SFC
            ON SFC.t_pdno = OPS.t_pdno
            AND SFC.t_opno = OPS.MinOpenOp                  
      WHERE WHINP.t_koor IN (@OrderType_Production, @OrderType_Purchase, @OrderType_Sales)
            AND (
                        (WHINP.t_koor = @OrderType_Purchase AND WHINP.t_kotr = @Receipt)
                        OR
                        (WHINP.t_koor = @OrderType_Sales AND WHINP.t_kotr = @Issue)
                        OR
                        (WHINP.t_koor = @OrderType_Production AND WHINP.t_kotr = @Receipt)
                        OR
                        (WHINP.t_koor = @OrderType_ProjectManual AND WHINP.t_kotr = @Issue)
                  )
      )ORD
      ON ORD.t_item = BOM.KeyItem
WHERE (
            (IBD.t_kitm = 1 AND @Flavor = @Flavor_MRP_WithPurchasedComponents)
            OR
            (@Flavor <> @Flavor_MRP_WithPurchasedComponents)
      )

ORDER BY LTRIM(RTRIM(BOM.TopLevelParent)),
      BOM.KeyItemLevel,
      LTRIM(RTRIM(BOM.KeyItem))
0
 
jsgrosskopfIS ManagerAuthor Commented:
Thanks so much for all the info. It's a lot and I'll have to spend a little time changing the fields to work with our mfg environment on ivc4 but it's a great reference tool .Thanks again
0
 
jsgrosskopfIS ManagerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for jsgrosskopf's comment #a40642702

for the following reason:

Thanks for the solution
0
 
UnifiedISCommented:
My answer should be accepted as the solution, it solved the original issue.
0
 
jsgrosskopfIS ManagerAuthor Commented:
That's what I thought I clicked....I'tt try again
0
 
jsgrosskopfIS ManagerAuthor Commented:
accepted as the solution, it solved the original issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.