Link to home
Start Free TrialLog in
Avatar of Joe Grosskopf
Joe Grosskopf

asked on

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'

Avatar of UnifiedIS

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.
Avatar of Joe Grosskopf


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
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
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.
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()
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).
Now we are getting 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.
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 you think that will work?
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.
Avatar of UnifiedIS

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.
Very good solution. Helped with an issue we've been having for a long time