Yeah I have no idea what you're saying below. Sorry. :-)
But V8 and E9 will certainly be representing BOMs using the same schema: just a set of related PartMtl records in a parent-child relationship.
If you're using MSSQL, you could do a Custom Report in Crystal that does a query like this, which will give you a nice BOM sorted correctly as many levels deep as you need to go. I made this a stored procedure so it wasn't embedded directly in the report in case I needed to change the logic in some way (which I did, as there was a bug in it).
-bws
DECLARE @PADDING INT
DECLARE @INDENTCHAR CHAR(1)
SET @PADDING = 8;
SET @INDENTCHAR = '.';
WITH BOM(company,partnum,indentedmtlpartnum,revisionnum,assy,partdesc,mtlseq,mtlseqpad,mtlpartnum,uom,qtyper,bubblenum,mfgr,mfgrpartnum,bomlevel,sortkey)
AS
(
SELECT pm1.company,pm1.partnum,CONVERT(VARCHAR,REPLICATE(@INDENTCHAR,1) + pm1.mtlpartnum),
dbo.API_funcGetCurrentPartRev(pm1.mtlpartnum,@COMPANY),p1.method,
CONVERT(VARCHAR(1024),p1.partdescription),pm1.mtlseq,
REPLICATE('0',@PADDING - LEN(CAST(pm1.mtlseq AS VARCHAR))) + CAST(pm1.mtlseq AS VARCHAR),
pm1.mtlpartnum,p1.ium,pm1.qtyper,
pm1.bubblenum,p1.shortchar01,p1.shortchar02,
1 as bomlevel,
CONVERT(VARCHAR(1024),REPLICATE('0',@PADDING - LEN(CAST(pm1.mtlseq AS VARCHAR))) + CAST(pm1.mtlseq AS VARCHAR))
FROM partmtl pm1
INNER JOIN part p1 ON p1.partnum = pm1.mtlpartnum
WHERE pm1.partnum = @PARTNUM AND pm1.revisionnum = @PARTREV AND pm1.company = @COMPANY AND p1.company = @COMPANY
UNION ALL
SELECT pm2.company,pm2.partnum,CONVERT(VARCHAR,REPLICATE(@INDENTCHAR,bomlevel+1) + pm2.mtlpartnum),--
-- CONVERT(VARCHAR(8),pm2.revisionnum),
dbo.API_funcGetCurrentPartRev(pm2.mtlpartnum,@COMPANY),
p2.method,
CONVERT(VARCHAR(1024),p2.partdescription),pm2.mtlseq,
REPLICATE('0',@PADDING - LEN(CAST(pm2.mtlseq AS VARCHAR))) + CAST(pm2.mtlseq AS VARCHAR),
pm2.mtlpartnum,p2.ium,pm2.qtyper,
pm2.bubblenum,p2.shortchar01,p2.shortchar02,
bomlevel + 1,
CONVERT(VARCHAR(1024), sortkey + '.' + REPLICATE('0',@PADDING - LEN(CAST(pm2.mtlseq AS VARCHAR))) + CAST(pm2.mtlseq AS VARCHAR))
FROM partmtl pm2
INNER JOIN part p2 ON p2.partnum = pm2.mtlpartnum
INNER JOIN BOM b ON pm2.partnum = b.mtlpartnum
WHERE pm2.company = @COMPANY and p2.company = @COMPANY
and pm2.revisionnum = dbo.API_funcGetCurrentPartRev(b.mtlpartnum,@COMPANY)
)
SELECT company,partnum,indentedmtlpartnum,revisionnum,partdesc,assy,mtlseq,mtlseqpad,mtlpartnum,uom,qtyper,bubblenum,mfgr,mfgrpartnum,bomlevel,sortkey from BOM
ORDER BY sortkey ASC;
You'll also need a custom function to return the current approved revision for a given part, as this is not explicitly stored in the BOM. My function is below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[API_funcGetCurrentPartRev]
(
-- Add the parameters for the function here
@PARTNUM varchar(32), @COMPANY varchar(8)
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @CURRENTREV VARCHAR(8);
SELECT @CURRENTREV = revisionnum from dbo.partrev where
partnum = @PARTNUM and
partrev.effectivedate <= getdate() and
partrev.approveddate <= getdate() and
approved = 1
order by effectivedate asc
RETURN @CURRENTREV
END
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Len Hartka
Sent: Friday, February 19, 2010 12:59 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM Change Log
Good Day:
Sun Is on M2K (Informix) but,
Going to: Epicor 9, 9.04.505B ( maybe 9.05), SQL (live 7/1/2010-
maybe 8/1/10)
Is this also true in E9?
I hope the statement below, in red, does not mean what I
think it does. I think it is saying that the BOM file is not simple
lines, but lines that are modified by the line above it. Does that mean
if I create a report on the file, I will get components listed 2-3-4-5
times because that component has been changed and the end result is not
saved; so if I change the quantity from 4 to 8 to 6, the entries will be
4, then +4, then -2 ( and NOT just one line for 6)?????
Although if it does, it would explain a lot about the weird way MOM
workbench works in E9.
BOMs are stored indirectly in Vantage -- there is no single "BOM
Record", as the BOM is build up of PartMtl entries that relate to one
another hierarchically.
len.hartka@...
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, February 18, 2010 5:01 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM Change Log
Well BOMs are only changed via ECO groups in Engineering Workbench, so
you get that change logging.
You have to check out the revision, make changes to the MOM (Method of
Manufacture, BOM + Bill of Operations), and check it back in. When you
do, you can indicate the purpose of your changes. There's also a utility
under Engineering/General Ops to compare two revisions.
There is no magic master change log that gets purged every week. You can
use the BAM system to tell the system to create entries in the ChgLog
table when particular fields (or all fields) on a given table change,
but you'll want to be careful with that or you'll wind up with a very
large chglog table with lots of noise in it.
BOMs are stored indirectly in Vantage -- there is no single "BOM
Record", as the BOM is build up of PartMtl entries that relate to one
another hierarchically.
Purging chglog would require running the Database Purge/Summarize
program (which would have much larger effects :-), or you could purge
chglog yourself.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~
www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of drew.pete
Sent: Wednesday, February 17, 2010 2:16 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BOM Change Log
Is there a change log in epicor(8.03.408b) that tracks changes to BOMs?
If not, is there a master change log somewhere that gets written to with
every change in the db and purged once a week?
Thanks,
Drew
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo
<http://groups.yahoo.com/group/vantage/linksYahoo> ! Groups Links
This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.
[Non-text portions of this message have been removed]
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
But V8 and E9 will certainly be representing BOMs using the same schema: just a set of related PartMtl records in a parent-child relationship.
If you're using MSSQL, you could do a Custom Report in Crystal that does a query like this, which will give you a nice BOM sorted correctly as many levels deep as you need to go. I made this a stored procedure so it wasn't embedded directly in the report in case I needed to change the logic in some way (which I did, as there was a bug in it).
-bws
DECLARE @PADDING INT
DECLARE @INDENTCHAR CHAR(1)
SET @PADDING = 8;
SET @INDENTCHAR = '.';
WITH BOM(company,partnum,indentedmtlpartnum,revisionnum,assy,partdesc,mtlseq,mtlseqpad,mtlpartnum,uom,qtyper,bubblenum,mfgr,mfgrpartnum,bomlevel,sortkey)
AS
(
SELECT pm1.company,pm1.partnum,CONVERT(VARCHAR,REPLICATE(@INDENTCHAR,1) + pm1.mtlpartnum),
dbo.API_funcGetCurrentPartRev(pm1.mtlpartnum,@COMPANY),p1.method,
CONVERT(VARCHAR(1024),p1.partdescription),pm1.mtlseq,
REPLICATE('0',@PADDING - LEN(CAST(pm1.mtlseq AS VARCHAR))) + CAST(pm1.mtlseq AS VARCHAR),
pm1.mtlpartnum,p1.ium,pm1.qtyper,
pm1.bubblenum,p1.shortchar01,p1.shortchar02,
1 as bomlevel,
CONVERT(VARCHAR(1024),REPLICATE('0',@PADDING - LEN(CAST(pm1.mtlseq AS VARCHAR))) + CAST(pm1.mtlseq AS VARCHAR))
FROM partmtl pm1
INNER JOIN part p1 ON p1.partnum = pm1.mtlpartnum
WHERE pm1.partnum = @PARTNUM AND pm1.revisionnum = @PARTREV AND pm1.company = @COMPANY AND p1.company = @COMPANY
UNION ALL
SELECT pm2.company,pm2.partnum,CONVERT(VARCHAR,REPLICATE(@INDENTCHAR,bomlevel+1) + pm2.mtlpartnum),--
-- CONVERT(VARCHAR(8),pm2.revisionnum),
dbo.API_funcGetCurrentPartRev(pm2.mtlpartnum,@COMPANY),
p2.method,
CONVERT(VARCHAR(1024),p2.partdescription),pm2.mtlseq,
REPLICATE('0',@PADDING - LEN(CAST(pm2.mtlseq AS VARCHAR))) + CAST(pm2.mtlseq AS VARCHAR),
pm2.mtlpartnum,p2.ium,pm2.qtyper,
pm2.bubblenum,p2.shortchar01,p2.shortchar02,
bomlevel + 1,
CONVERT(VARCHAR(1024), sortkey + '.' + REPLICATE('0',@PADDING - LEN(CAST(pm2.mtlseq AS VARCHAR))) + CAST(pm2.mtlseq AS VARCHAR))
FROM partmtl pm2
INNER JOIN part p2 ON p2.partnum = pm2.mtlpartnum
INNER JOIN BOM b ON pm2.partnum = b.mtlpartnum
WHERE pm2.company = @COMPANY and p2.company = @COMPANY
and pm2.revisionnum = dbo.API_funcGetCurrentPartRev(b.mtlpartnum,@COMPANY)
)
SELECT company,partnum,indentedmtlpartnum,revisionnum,partdesc,assy,mtlseq,mtlseqpad,mtlpartnum,uom,qtyper,bubblenum,mfgr,mfgrpartnum,bomlevel,sortkey from BOM
ORDER BY sortkey ASC;
You'll also need a custom function to return the current approved revision for a given part, as this is not explicitly stored in the BOM. My function is below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[API_funcGetCurrentPartRev]
(
-- Add the parameters for the function here
@PARTNUM varchar(32), @COMPANY varchar(8)
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @CURRENTREV VARCHAR(8);
SELECT @CURRENTREV = revisionnum from dbo.partrev where
partnum = @PARTNUM and
partrev.effectivedate <= getdate() and
partrev.approveddate <= getdate() and
approved = 1
order by effectivedate asc
RETURN @CURRENTREV
END
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Len Hartka
Sent: Friday, February 19, 2010 12:59 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM Change Log
Good Day:
Sun Is on M2K (Informix) but,
Going to: Epicor 9, 9.04.505B ( maybe 9.05), SQL (live 7/1/2010-
maybe 8/1/10)
Is this also true in E9?
I hope the statement below, in red, does not mean what I
think it does. I think it is saying that the BOM file is not simple
lines, but lines that are modified by the line above it. Does that mean
if I create a report on the file, I will get components listed 2-3-4-5
times because that component has been changed and the end result is not
saved; so if I change the quantity from 4 to 8 to 6, the entries will be
4, then +4, then -2 ( and NOT just one line for 6)?????
Although if it does, it would explain a lot about the weird way MOM
workbench works in E9.
BOMs are stored indirectly in Vantage -- there is no single "BOM
Record", as the BOM is build up of PartMtl entries that relate to one
another hierarchically.
len.hartka@...
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, February 18, 2010 5:01 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM Change Log
Well BOMs are only changed via ECO groups in Engineering Workbench, so
you get that change logging.
You have to check out the revision, make changes to the MOM (Method of
Manufacture, BOM + Bill of Operations), and check it back in. When you
do, you can indicate the purpose of your changes. There's also a utility
under Engineering/General Ops to compare two revisions.
There is no magic master change log that gets purged every week. You can
use the BAM system to tell the system to create entries in the ChgLog
table when particular fields (or all fields) on a given table change,
but you'll want to be careful with that or you'll wind up with a very
large chglog table with lots of noise in it.
BOMs are stored indirectly in Vantage -- there is no single "BOM
Record", as the BOM is build up of PartMtl entries that relate to one
another hierarchically.
Purging chglog would require running the Database Purge/Summarize
program (which would have much larger effects :-), or you could purge
chglog yourself.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~
www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of drew.pete
Sent: Wednesday, February 17, 2010 2:16 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BOM Change Log
Is there a change log in epicor(8.03.408b) that tracks changes to BOMs?
If not, is there a master change log somewhere that gets written to with
every change in the db and purged once a week?
Thanks,
Drew
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo
<http://groups.yahoo.com/group/vantage/linksYahoo> ! Groups Links
This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.
[Non-text portions of this message have been removed]
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links