I was able to write stored procedure for SQL server that creates
temporary tables. Then I used this procedure for the report. I do not
know how to attach the files so I'll post it here.
You need to run this in SQL Query Analyzer for Vantage database.
*************START*****************************
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE spIndentedBOM_ITT
@part varchar(20)
AS
BEGIN
--declare @part varchar(20)
--set @part='1026'
declare @levelnum int
set @levelnum=0
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(8000)
)
insert into #bomtempo1
select partrev.company,'', partrev.revisionnum,partrev.effectivedate,
1,1,@part,@levelnum,'00',1,'',@part
from partrev with (nolock) where partrev.partnum=@part and
partrev.effectivedate in
(SELECT MAX(partrev.effectivedate) from partrev with (nolock) 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,partrev.effectived
ate,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 partrev with (nolock) on
partmtl.partnum=partrev.partnum and
partmtl.revisionnum=partrev.revisionnum
and partmtl.company=partrev.company
and partrev.partnum + cast(partrev.effectivedate as varchar
(40)) in
(SELECT partrev.partnum+cast(MAX(partrev.effectivedate) as
varchar(40)) as latestdate
FROM partrev with (nolock)
GROUP BY partrev.partnum))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
create table #bomtempo2(
company varchar(8),
lvl int,
pullasasm smallint,
viewasasm smallint,
part varchar(20),
revision varchar(5),
partdescription varchar(50),
effectivedate datetime,
ium varchar(2),
typecode varchar(2),
nonstock tinyint,
prodcode varchar(4),
qtyper decimal(15,5),
FindNo varchar(15),
RefNo varchar(255),
Alt varchar(255),
Comments varchar(255),
mfgcomments varchar(1000),
nodes varchar(8000),
filename varchar(255)
)
insert into #bomtempo2
select isnull(#bomtempo1.company,'') as
company,lvl,pullasasm,viewasasm,child as part,revision,
cast(part.partdescription as varchar(50)) as
partdescription,effectivedate,part.ium,part.typecode,part.nonstock,par
t.prodcode,qtyper,
FindNo=
case
when (charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar(255))))
>0
then
case
when (charindex('REF:',cast(#bomtempo1.mfgcomment as varchar
(255))))>0 then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar
(255)))+5,charindex('REF:',cast(#bomtempo1.mfgcomment as varchar
(255))) -charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar
(255)))-5) as varchar(255))),len(rtrim(cast(substring(cast
(#bomtempo1.mfgcomment as varchar(255)),charindex('FIND:',cast
(#bomtempo1.mfgcomment as varchar(255)))+5,charindex('REF:',cast
(#bomtempo1.mfgcomment as varchar(255))) -charindex('FIND:',cast
(#bomtempo1.mfgcomment as varchar(255)))-5) as varchar(255))))-1) as
varchar(10))
else
case
when (charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar
(255))))>0 then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar
(255)))+5,charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar
(255))) -charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar
(255)))-5) as varchar(255))),len(rtrim(cast(substring(cast
(#bomtempo1.mfgcomment as varchar(255)),charindex('FIND:',cast
(#bomtempo1.mfgcomment as varchar(255)))+5,charindex('ALT:',cast
(#bomtempo1.mfgcomment as varchar(255))) -charindex('FIND:',cast
(#bomtempo1.mfgcomment as varchar(255)))-5) as varchar(255))))-1) as
varchar(10))
else
case
when (charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))))>0 then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar
(255)))+5,charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))) -charindex('FIND',cast(#bomtempo1.mfgcomment as varchar
(255)))-5) as varchar(255))),len(rtrim(cast(substring(cast
(#bomtempo1.mfgcomment as varchar(255)),charindex('FIND:',cast
(#bomtempo1.mfgcomment as varchar(255)))+5,charindex('COMMENT',cast
(#bomtempo1.mfgcomment as varchar(255))) -charindex('FIND',cast
(#bomtempo1.mfgcomment as varchar(255)))-5) as varchar(255))))-1) as
varchar(10))
else cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment
as varchar(255)),charindex('FIND:',cast(#bomtempo1.mfgcomment as
varchar(255)))+5,len(cast(#bomtempo1.mfgcomment as varchar(255)))-
charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as
varchar(255))),len(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('FIND:',cast(#bomtempo1.mfgcomment as varchar
(255)))+5,len(cast(#bomtempo1.mfgcomment as varchar(255)))-charindex
('FIND:',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as varchar
(255))))-0) as varchar(10))
end
end
end
else '0'
end,
RefNo=
case
when (charindex('REF:',cast(#bomtempo1.mfgcomment as varchar(255))))
>0
then
case
when (charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar
(255))))>0 then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('REF:',cast(#bomtempo1.mfgcomment as varchar
(255)))+4,charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar
(255))) -charindex('REF:',cast(#bomtempo1.mfgcomment as varchar
(255)))-4) as varchar(255))),len(rtrim(cast(substring(cast
(#bomtempo1.mfgcomment as varchar(255)),charindex('REF:',cast
(#bomtempo1.mfgcomment as varchar(255)))+4,charindex('ALT:',cast
(#bomtempo1.mfgcomment as varchar(255))) -charindex('REF:',cast
(#bomtempo1.mfgcomment as varchar(255)))-4) as varchar(255))))-1) as
varchar(255))
else
case
when (charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))))>0 then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('REF:',cast(#bomtempo1.mfgcomment as varchar
(255)))+4,charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))) -charindex('REF',cast(#bomtempo1.mfgcomment as varchar(255)))-
4) as varchar(255))),len(rtrim(cast(substring(cast
(#bomtempo1.mfgcomment as varchar(255)),charindex('REF:',cast
(#bomtempo1.mfgcomment as varchar(255)))+4,charindex('COMMENT',cast
(#bomtempo1.mfgcomment as varchar(255))) -charindex('REF',cast
(#bomtempo1.mfgcomment as varchar(255)))-4) as varchar(255))))-1) as
varchar(255))
else cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment
as varchar(255)),charindex('REF:',cast(#bomtempo1.mfgcomment as
varchar(255)))+4,len(cast(#bomtempo1.mfgcomment as varchar(255)))-
charindex('REF:',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as
varchar(255))),len(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('REF:',cast(#bomtempo1.mfgcomment as varchar
(255)))+4,len(cast(#bomtempo1.mfgcomment as varchar(255)))-charindex
('REF:',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as varchar
(255))))-0) as varchar(255))
end
end
else ''
end,
Alt=
case
when (charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar(255))))
>0
then
case
when (charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))))>0 then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar
(255)))+4,charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))) -charindex('ALT',cast(#bomtempo1.mfgcomment as varchar(255)))-
4) as varchar(255))),len(rtrim(cast(substring(cast
(#bomtempo1.mfgcomment as varchar(255)),charindex('ALT:',cast
(#bomtempo1.mfgcomment as varchar(255)))+4,charindex('COMMENT',cast
(#bomtempo1.mfgcomment as varchar(255))) -charindex('ALT',cast
(#bomtempo1.mfgcomment as varchar(255)))-4) as varchar(255))))-1) as
varchar(255))
else cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment
as varchar(255)),charindex('ALT:',cast(#bomtempo1.mfgcomment as
varchar(255)))+4,len(cast(#bomtempo1.mfgcomment as varchar(255)))-
charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as
varchar(255))),len(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as
varchar(255)),charindex('ALT:',cast(#bomtempo1.mfgcomment as varchar
(255)))+4,len(cast(#bomtempo1.mfgcomment as varchar(255)))-charindex
('ALT:',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as varchar
(255))))-0) as varchar(255))
end
else ''
end,
Comments=
case
when (charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255))))>0
then
cast(left(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as varchar
(255)),charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255)))+7,len(cast(#bomtempo1.mfgcomment as varchar(255)))-charindex
('COMMENT',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as varchar
(255))),len(rtrim(cast(substring(cast(#bomtempo1.mfgcomment as varchar
(255)),charindex('COMMENT',cast(#bomtempo1.mfgcomment as varchar
(255)))+7,len(cast(#bomtempo1.mfgcomment as varchar(255)))-charindex
('COMMENT',cast(#bomtempo1.mfgcomment as varchar(255)))-0) as varchar
(255))))-0) as varchar(255))
else ''
end,
cast(#bomtempo1.mfgcomment as varchar(255)) as
mfgcomments,nodes,drawings.filename
from #bomtempo1 with (nolock) inner join part with (nolock) on
#bomtempo1.child=part.partnum and #bomtempo1.company=part.company
left outer join drawings with (nolock) on
#bomtempo1.company=drawings.company and
#bomtempo1.child=drawings.key1 and #bomtempo1.revision=drawings.key2
and
drawings.relatedtofile='PartRev'
order by nodes
select * from #bomtempo2
drop table #bomtempo1
drop table #bomtempo2
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*************END*******************************
--- In
vantage@yahoogroups.com, "Rudy Rodriguez" <rrodriguez@...>
wrote:
>
> I would appreciate this as well please!
> Thanks,
> Rudy
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of Jasper Recto
> Sent: Monday, October 16, 2006 12:54 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] Re: Has anyone created a Crystal BOM listing.
>
>
>
> Me Too Please!
>
> -----Original Message-----
> From: dsirow [mailto:dan@... <mailto:dan%40iccparts.com> ]
> Sent: Monday, October 16, 2006 1:39 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: Has anyone created a Crystal BOM listing.
>
> I would also like a copy of this report.
> Dan
> Independent Components Corp.
>
> --- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com,
> "Toby Boogerd" <tboogerd@> wrote:
> >
> > Yes, I will send you this report.
> >
> >
> >
> >
> >
> > Toby Boogerd
> > Information Systems
> > 712-324-4854 x1119
> >
> > NOTICE: This email may contain confidential and proprietary
> information
> > of Rosenboom Machine & Tool, Inc. By opening any enclosed files,
> the
> > recipient agrees not to use, reproduce, disclose, or manufacture
> its
> > contents, in whole or in part, without prior written consent of
> > Rosenboom Machine & Tool, Inc.
> >
> > ________________________________
> >
> > From: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
> [mailto: vantage@yahoogroups <mailto:vantage%
40yahoogroups.com> .com] On
>
> Behalf
> > Of Dave Olender
> > Sent: Thursday, October 05, 2006 8:06 AM
> > To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
> > Subject: [Vantage] Has anyone created a Crystal BOM listing.
> >
> >
> >
> > I have been trying to create a Crystal version of the BOM Listing
> > Report and not having much luck.
> >
> > I have linked ECORev -> ECOMtl tables. The ECOMtl table has
> > MtlPartNum field that lets me see the material required for the
> > Parent PartNum however I am unable to find a MtlPartNum Revision
> > (required to lookup the methods of the material requirement).
> >
> > I also looked at the Part -> PartMtl tables to see if that was
any
> > different but it is the same.
> >
> > So at this point I am only able to list the 1st level components
> for
> > a part number.
> >
> > If anyone has cracked this nut, I would certainly appreciate some
> > advice.
> >
> > Thanks in advance,
> > Dave Olender dolender@
> > <mailto:dolender%40olenderconsulting.com>
> > Ph 651-246-3281
> >
> >
> >
> >
> >
> > [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]
>