Hi everyone,
For those of you who keeping the database in SQL Server the following T-SQL
code will scan the latest BOM revision of each part and sub-part down to all
levels and display the indented BOM in a flat table.
Greetings from Greece,
Vassilis
T-SQL CODE:
declare @part varchar(20)
set @part = 'PARTNUM-XX'
if (object_id('tempdb..#tmaxeffdate') is null)
create table #tmaxeffdate(
company varchar(8),
partnum varchar(20),
effectivedate datetime
) else truncate table #tmaxeffdate
insert into #tmaxeffdate
select company, partnum, max(effectivedate)
from partrev with (nolock)
where approved = 1
group by company, partnum
if (object_id('tempdb..#tlastrev') is null)
create table #tlastrev(
company varchar(8),
partnum varchar(20),
revisionnum varchar(12),
effectivedate datetime
) else truncate table #tlastrev
insert into #tlastrev
select partrev.company, partrev.partnum, max(partrev.revisionnum),
partrev.effectivedate
from partrev with (nolock) inner join #tmaxeffdate
on partrev.company = #tmaxeffdate.company and partrev.partnum =
#tmaxeffdate.partnum
and partrev.effectivedate = #tmaxeffdate.effectivedate
where partrev.approved = 1
group by partrev.company, partrev.partnum, partrev.effectivedate
truncate table #tmaxeffdate
insert into #tmaxeffdate
select company, partnum, max(effectivedate)
from partrev with (nolock)
where approved = 0 and company + partnum not in (select company + partnum
from #tlastrev)
group by company, partnum
insert into #tlastrev
select partrev.company, partrev.partnum, max(partrev.revisionnum),
partrev.effectivedate
from partrev with (nolock) inner join #tmaxeffdate
on partrev.company = #tmaxeffdate.company and partrev.partnum =
#tmaxeffdate.partnum
and partrev.effectivedate = #tmaxeffdate.effectivedate
where partrev.approved = 0
group by partrev.company, partrev.partnum, partrev.effectivedate
if not exists(select partnum from part with (nolock) where partnum=@part)
begin
raiserror('Wrong part number',11,1)
return
end
declare @levelnum int
set @levelnum = 0
if (object_id('tempdb..#bomtempo1') is null)
create table #bomtempo1(
company varchar(8),
parent varchar(20),
revision varchar(5),
effectivedate datetime,
pullasasm smallint,
viewasasm smallint,
child varchar(20),
lvl int,
mtlseq varchar(8),
qtyper decimal(15,5),
mfgcomment varchar(1000),
nodes varchar(4000)
) else truncate table #bomtempo1
insert into #bomtempo1
select partrev.company, '', partrev.revisionnum, partrev.effectivedate, 1,
1, @part, @levelnum, '00', 1, '', @part
from partrev with (nolock)
inner join #tlastrev
on #tlastrev.company = partrev.company
and #tlastrev.partnum = partrev.partnum
and #tlastrev.revisionnum = partrev.revisionnum
where partrev.partnum = @part
while (exists (select * from #bomtempo1 with (nolock) where lvl=@levelnum))
begin
insert into #bomtempo1
select partmtl.company, partmtl.partnum, partmtl.revisionnum,
#tlastrev.effectivedate, partmtl.pullasasm, partmtl.viewasasm,
partmtl.mtlpartnum,
@levelnum + 1, cast(partmtl.mtlseq as varchar(8)), partmtl.qtyper,
partmtl.mfgcomment, #bomtempo1.nodes + '-' + partmtl.mtlpartnum
from partmtl with (nolock)
inner join #tlastrev with (nolock)
on partmtl.company = #tlastrev.company and partmtl.partnum =
#tlastrev.partnum and partmtl.revisionnum = #tlastrev.revisionnum
inner join #bomtempo1 with (nolock)
on partmtl.partnum = #bomtempo1.child and partmtl.company =
#bomtempo1.company
where #bomtempo1.lvl = @levelnum and #bomtempo1.viewasasm = 1
set @levelnum = @levelnum + 1
end
select * from #bomtempo1 order by nodes
_____
From:
vantage@yahoogroups.com [mailto:
vantage@yahoogroups.com] On Behalf Of
Tom J. Christie
Sent: Thursday, May 16, 2013 6:19 PM
To:
vantage@yahoogroups.com
Subject: RE: [Vantage] Indented Bills of Materials
If you are using SQL, there are some other options.
You have to consider a few complications though:
(1) Which revision do you want to explode through?
(2) What about revisions in ECOs?
Tom Christie | Information Technology Manager | AGM Container Controls, Inc.
|
tchristie@... <mailto:tchristie%40agmcontainer.com>
<mailto:
tchristie@... <mailto:tchristie%40agmcontainer.com> > |
t: 520.881.2130 ext 2176
From:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Steve
Sent: Thursday, May 16, 2013 4:35 AM
To:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Indented Bills of Materials
Did you include approved revisions in your level structure? If you did not
you are going to get all revision levels in your bill.
Steve
From:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> [mailto:
vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>] On
Behalf Of Winter, Patrick
Sent: Wednesday, May 15, 2013 8:04 PM
To:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Indented Bills of Materials
If you use an outer join for the sixth and you bill only has 5 levels the
outer join allows it to work. It simply won't return any data in the fields.
Patrick
On May 15, 2013, at 6:01 PM, "Cathy" <
cathy@...
<mailto:cathy%40embeddedarm.com> <mailto:cathy%40embeddedarm.com>
<mailto:cathy%40embeddedarm.com> > wrote:
I guess what I meant to say - if you added 5 partmtl tables as alias and you
add another level to your BOM -
Would you have to modify the report and add another partmtl alias for the
sixth level?
From:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Winter, Patrick
Sent: Wednesday, May 15, 2013 3:35 PM
To:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Indented Bills of Materials
You don't have to assume the level is fixed if you use Left Outer Joins.
Patrick
On May 15, 2013, at 4:25 PM, "Cathy" <
cathy@...
<mailto:cathy%40embeddedarm.com> <mailto:cathy%40embeddedarm.com>
<mailto:cathy%40embeddedarm.com> <mailto:cathy%40embeddedarm.com> > wrote:
The problem with indented BOM, is you have to assume the number of levels of
is fixed when you add the PartMtl tables as alias's.
Since there is already and indented BOM Listing, just modify that report to
add On Hand Qty, Demand and Open PO's.
An Indented BOM is not your usual report and usually has some special
programming inside so you don't have to assume that your number of levels is
a fixed number.
From:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> [mailto:
vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Ken Dumesnil
Sent: Wednesday, May 15, 2013 10:45 AM
To:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Indented Bills of Materials
Cathy,
I'm trying to come up with a different version of the Part Availability
report. So I need to start with the indented BOM and then look at On Hand
Qty, Demand (Firmed and unFirmed), open PO's on shortages etc.
Ken
________________________________
From: Cathy <
cathy@... <mailto:cathy%40embeddedarm.com>
<mailto:cathy%40embeddedarm.com> <mailto:cathy%40embeddedarm.com>
<mailto:cathy%40embeddedarm.com> <mailto:cathy%40embeddedarm.com> >
To:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Sent: Wednesday, May 15, 2013 12:24 PM
Subject: RE: [Vantage] Indented Bills of Materials
What are you missing from the BOM Listing under Engineering?
From:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
Ken Dumesnil
Sent: Wednesday, May 15, 2013 8:09 AM
To:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Indented Bills of Materials
I'm trying to create a BAQ to view the indented Bill of Materials. I'm
using the PartMtl table that shows me the top level part and the MtlPartNum
(Sub Assembly). Unfortunately this is only showing the 1st level of the
BOM but I need it to show all the parts on the Sub Assembly's (MtlPartNum)
as well. Of course this drill down though assembly's could be 5 or more
levels deep.
I found the PartBom table but it has nothing in it so I assume that this is
a temp table for structuring the BOM used by reports.
Does anyone know how to get a complete view of an indented BOM in a BAQ?
Thanks,
Ken
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]