SQL BOM query (pt 2)

I posted a question last week about this and did get an answer that worked. but I need to find tune my query. I have a recursive query that gets my BOM however it is not indented. I see several items on lvl 0,1,2 however it does not put the levels in indented form.  My query results show:

MainAssy      SubAssy                t_item               t_qana      lvl      YearExp
4586-2              3761-4                A022202          1.69      2      1753
4586-2              4564               A023502          1.75      2      1753
4586-2             4586                3761-4                   1              1      1753
4586-2            4586                     4564                            1      1      1753
4586-2           4586                     4586                     0              0      1753
4586-2            4586                    4587                            1      1      1753
4586-2            4587                   A025602                 2.75      2      1753
4586-2            64889          64889                             1      0      1753
4586-2           64889            A021702                  1.3      1      1753

The "A0" number are level 2 but A025602 in part 4587 and A023502 is in part 4564. This result does not indicate that.

Is there any way to rewrite the script so that it shows the levels in correct order meaning main, assy, sub assy, item etc.

Here is the script I am using now:

    with BOMCTE AS (
  SELECT t_mitm as MainAssy, t_sitm as SubAssy, t_sitm as t_item, t_qana, 0 AS lvl, year(t_exdt) as 'YearExp'
  FROM ttibom010100
  where year(t_exdt) = 1753
  SELECT usr.MainAssy, mgr.t_mitm, mgr.t_sitm, mgr.t_qana, usr.lvl +1 AS lvl, YearExp
    INNER JOIN ttibom010100 AS mgr
      ON usr.t_item = mgr.t_mitm
        where year(t_exdt) = 1753

SELECT distinct *
  where MainAssy = '4586-2'

jsgrosskopfIS ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The procs I provided on the other question lay out the data such that indenting them in your presentation layer is fairly simple using groups and some suppression. Did you attempt to use that? It's a lot different but shouldn't require much modification to work for your scenario.
jsgrosskopfIS ManagerAuthor Commented:
Thanks. I have tried that yet...was hoping a simple one line entry would fix my issue but I guess I need to bite-the-bullet and try setting up the script using what you sent. I'll do that and report back in a day or two. Thanks Again
jsgrosskopfIS ManagerAuthor Commented:
I am entering the BOM procedure and making changes as needed. 2 questions if you do not mind.

1.) The script references table ttcibd001100. In c4, we do not have (or at least we do not use that table). Can you tell em what that table is? Is it the Item Master? I would like to change to the correct c4 table

2.) After entering the procedure and running it, the variables are itemcsv, item1 and item2. I believe itemcsv is the sepeerator I wish to use but I do not understand the item1 and item2. I thought I would enter 1 item number and get teh BOM for that one item
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Yes, ttcibd001100 is the item master (for logistics company 100).
item 1 and item 2 is a means to allow a range of items. Item1 = 10000, Item2 = 10004 will return data for 10000, 10001, 10002, 10003, 10004.
To do just a single item, set the itemcsv = '10000' (or enter 10000 in both item1 and item2).  You could also just set item2 = item1 when item2 is null or blank.

My customers often have multiple items in a tight sequence so the item range option is convenient in those cases.
jsgrosskopfIS ManagerAuthor Commented:
Thanks again for working with me on this. I did what you suggested....

USE [baanivc4db]

DECLARE      @return_value int

EXEC      @return_value = [dbo].[BOM_10Levels]
            @ItemCSV = N'8730',
            @Item1 = N'',
            @Item2 = N''

SELECT      'Return Value' = @return_value


Hoping to get the BOM for item 8730

I get:

KeyItem      KeyItemLevel      ImmediateParent      TopLevelParent      Level1Child      Level2Child      Level3Child      Level4Child      Level5Child      Level6Child      Level7Child      Level8Child      Level9Child      Level10Child
         8730      0      NULL               8730      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL


Return Value

Do you have any idea what I am missing? or what part of the SP I forgot to change for c4?
It looks like in BOM_10Levels, the link to get the child records is not returning anything.  
In 5C and LN, the item is a combined field consisting of a 9 character project section and then the item code.

First place to check would be the initial insert into @ERPBOM. In LN, they stopped using the 1970 epoch for the current BOM records so I only look for a future date. Try with it like this:
"AND (t_exdt > GETDATE() OR t_exdt = '1970-01-01')" instead of "t_exdt > GETDATE()"

      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()
jsgrosskopfIS ManagerAuthor Commented:
That is not the case. I changed it to:

      t_mitm varchar(50),
      t_sitm varchar(50)
INSERT INTO @ERPBOM (t_mitm, t_sitm)
SELECT t_mitm, t_sitm
FROM ttibom010100
WHERE year(t_exdt) = 1753

If I simply run that, I get 203070 rows affected. This tells me if it getting thh data but the link between what I am asking for and this 203,000 records is not happening.
Ok, that's good. @ERPBOM holds all the active parent/child relationships in the entire BOM table.

Please double-check that @Items and @BOM have records. @Items holds the top-level parent items from the parameter inputs. @BOM is like a seed table, prior to the loop, it should hold one record for each record in the @Items table.

I started to mention the 9 spaces that precedes the item code in LN.  Does that exist in your Baan? If not, then you'll need to get rid of that in the section that parses @ItemCSV (and where @Item1 and @Item2 are tweaked).
jsgrosskopfIS ManagerAuthor Commented:
Now we are getting somewhere...it was the 9 spaces. Now I have a lot of results  and I need to see how to translate that to get a report that my users can use.

The way I have laid it out to visually show the depth is to do a group by the Top Level Parent.
Then in the details section, I lay out level1Child, level2Child, Level3Child... going from left to right. Then for each of those fields, I conditionally suppress it from view.  So for level1child, it is suppressed when level2child is not null
Level2Child is suppressed when Level3Child is not null and so on. I've attached a couple screenshots to help explain this.
jsgrosskopfIS ManagerAuthor Commented:
K, Thanks again. I know once I get this one they are also going to want one that shows customized items so I'll have to incorporate the project number and project table. Hopefully, it is just a matter of changing the table then changing one of the @Item fields to point to the project number....do you think that will work?
jsgrosskopfIS ManagerAuthor Commented:
I need the qty of each item. I added it here:

INSERT INTO @ERPBOM (t_mitm, t_sitm, t_qana)
SELECT t_mitm, t_sitm, t_qana
FROM ttibom010100
WHERE year(t_exdt) = 1753

but how do I get the t_qana to show up in my level list? or is this something you do once it hits the report?
I'm not exactly sure what will need to change work with projects.  If a project has a list of items, then you can take the project number as a parameter, pull all the associated items and create a comma-separated string and then pass that to the BOM_10Levels proc.
I combine additional data with the proc that directly feeds the report.
Basically, it uses BOM_10Levels to pull the data into a temp table.
Then I join ttibom010100, the item master table, etc... to the temp table. Here is the source proc for the report that I attached previously. BOM data is loaded and then I connect to the item master on the KeyItem and get BOM detail by joining KeyItem to sitm and the Immediate parent to mitm. You'll have to tweak the indt/exdt criteria. Note that with LN (5C too) I trim the item fields because of the leading 9 spaces that are reserved for a project number.

2014-12-16 DBC Outputs a BOM for repair techs to easily identify items required to repair a tool

@Item varchar(50)



--DECLARE @Item varchar(50)
SET @Item = space(9) + UPPER(LTRIM(RTRIM(ISNULL(@Item, '03037'))))

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

      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),
      Sorter varchar(500)
INSERT INTO @BOM (KeyItem, KeyItemLevel, ImmediateParent, TopLevelParent, Level1Child, Level2Child, Level3Child, Level4Child, Level5Child, Level6Child, Level7Child, Level8Child, Level9Child, Level10Child)

      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'
      BOMDET.t_qana AS BOMQty,
      IBD_TOP.t_dsca AS TopLevelParentDescription
INNER JOIN ttcibd001100 IBD
      ON IBD.t_item = BOM.KeyItem
      AND IBD.t_csig <> '004'
      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)      
INNER JOIN ttcibd001100 IBD_TOP
      ON IBD_TOP.t_item = BOM.TopLevelParent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsgrosskopfIS ManagerAuthor Commented:
So far this is working great. Just wondering do you have any suggestion how to make the project number a variable? I was thinking of leaving Item1 and Item2 as is and changing itemCSV to project number. But looking through the script, i cannot see where I can tell itemCSV to match up to t_cprj. I already change the table to the customized BOM and added t_cprj as a variable, just need to query it. Thanks
What is the relationship between a project number and a bill of material?
jsgrosskopfIS ManagerAuthor Commented:
Custom Bills of material are created for projects. I think I actually got it. Instead of changing the ItemCSV, I just added a project number variable and then added the @Project = t_cprj to the "where" clause. I have an engineer checking it now. Thanks so much for this and your help.
jsgrosskopfIS ManagerAuthor Commented:
Very good solution. Helped with an issue we've been having for a long time
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.