EcoMlt? Blank Title 107866

Yahoo Archive
1
FYI, I put these schemas up a while back for a similar question. http://tech.groups.yahoo.com/group/vantage/files/Schemas/

There is one in there for the ECO table set.

From: Rob Bucek
Sent: Thursday, February 23, 2012 5:19 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] EcoMlt?


Jose,

Are you querying the ecomtl table? It has revisionnum in it.. wouldn't you query by the parents revnum?

Rob Bucek
Production Control Manager
PH: (715) 284-5376 ext 311
Mobile: (715)896-0590
FAX: (715)284-4084
[Description: cid:mailto:1.234354861%40web65412.mail.ac4.yahoo.com]<http://www.dsmfg.com/>
(Click the logo to view our site)<http://www.dsmfg.com/>

From: mailto:vantage%40yahoogroups.com [mailto:mailto:vantage%40yahoogroups.com] On Behalf Of Jose Gomez
Sent: Thursday, February 23, 2012 12:34 PM
To: Vantage
Subject: [Vantage] EcoMlt?

Hi All

If I have an EcoMtl record and it has PullAsAsmb =true
I'd like to run a query and get that assembly's EcoMtl records... AKA going
all the way down the levels, the problem that I am having is that EcoMtl
does not give you the Revision of the Part so I can't query the table
without that, how do i get what revision that EcoMtl is attached ot?

Thanks!~

*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: mailto:jose%40josecgomez.com<mailto:jose%40josecgomez.com>
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4827 - Release Date: 02/23/12


[Non-text portions of this message have been removed]
2
Hi All

If I have an EcoMtl record and it has PullAsAsmb =true
I'd like to run a query and get that assembly's EcoMtl records... AKA going
all the way down the levels, the problem that I am having is that EcoMtl
does not give you the Revision of the Part so I can't query the table
without that, how do i get what revision that EcoMtl is attached ot?

Thanks!~



*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*


[Non-text portions of this message have been removed]
3
I needed the same thing but in SQL so I wrote a stored function. You give the function a Partnum and gives back the active Rev. I'm not a SQL guru or anything but it works for me.

CREATE FUNCTION [dbo].[AGM_ActiveRev]
(
-- Add the parameters for the function here
@PartNum nvarchar(50)
)
RETURNS @Revision TABLE
(
[Revision] nvarchar(10)
)
AS
BEGIN
INSERT INTO @Revision
SELECT top 1
(CASE
WHEN (select sum(pr.approved) from PartRev as PR where PR.PartNum = @PartNum) = 0
THEN
(select top 1 RevisionNum
from PartRev as PR
where PR.PartNum = @PartNum and
PR.EffectiveDate < getdate()
order by effectivedate desc)

ELSE
(select top 1 RevisionNum
from PartRev as PR
where PR.PartNum =@PartNum and
PR.approved = 1 and
PR.EffectiveDate < getdate()
order by effectivedate desc)
END)
FROM PartRev
where partrev.PartNum = @PartNum
RETURN
END

GO


Tom Christie

Manufacturing Engineer

AGM Container Controls
3526 E Ft. Lowell Road
Tucson AZ, 85716
PH: 520-881-2130
FX: 520-881-4983
www.AGMcontainer.com<http://www.agmcontainer.com/>



[Non-text portions of this message have been removed]
4
I needed the same thing but in SQL so I wrote a stored function. You give the function a Partnum and gives back the active Rev. I'm not a SQL guru or anything but it works for me.

CREATE FUNCTION [dbo].[AGM_ActiveRev]
(
-- Add the parameters for the function here
@PartNum nvarchar(50)
)
RETURNS @Revision TABLE
(
[Revision] nvarchar(10)
)
AS
BEGIN
INSERT INTO @Revision
SELECT top 1
(CASE
WHEN (select sum(pr.approved) from PartRev as PR where PR.PartNum = @PartNum) = 0
THEN
(select top 1 RevisionNum
from PartRev as PR
where PR.PartNum = @PartNum and
PR.EffectiveDate < getdate()
order by effectivedate desc)

ELSE
(select top 1 RevisionNum
from PartRev as PR
where PR.PartNum =@PartNum and
PR.approved = 1 and
PR.EffectiveDate < getdate()
order by effectivedate desc)
END)
FROM PartRev
where partrev.PartNum = @PartNum
RETURN
END

GO


Tom Christie

Manufacturing Engineer

AGM Container Controls
3526 E Ft. Lowell Road
Tucson AZ, 85716
PH: 520-881-2130
FX: 520-881-4983
www.AGMcontainer.com<http://www.agmcontainer.com/>



[Non-text portions of this message have been removed]
5
Thanks Tom

*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*



On Thu, Feb 23, 2012 at 5:08 PM, Tom J. Christie <tchristie@...
> wrote:

> **
>
>
> I needed the same thing but in SQL so I wrote a stored function. You give
> the function a Partnum and gives back the active Rev. I'm not a SQL guru or
> anything but it works for me.
>
> CREATE FUNCTION [dbo].[AGM_ActiveRev]
> (
> -- Add the parameters for the function here
> @PartNum nvarchar(50)
> )
> RETURNS @Revision TABLE
> (
> [Revision] nvarchar(10)
> )
> AS
> BEGIN
> INSERT INTO @Revision
> SELECT top 1
> (CASE
> WHEN (select sum(pr.approved) from PartRev as PR where PR.PartNum =
> @PartNum) = 0
> THEN
> (select top 1 RevisionNum
> from PartRev as PR
> where PR.PartNum = @PartNum and
> PR.EffectiveDate < getdate()
> order by effectivedate desc)
>
> ELSE
> (select top 1 RevisionNum
> from PartRev as PR
> where PR.PartNum =@PartNum and
> PR.approved = 1 and
> PR.EffectiveDate < getdate()
> order by effectivedate desc)
> END)
> FROM PartRev
> where partrev.PartNum = @PartNum
> RETURN
> END
>
> GO
>
> Tom Christie
>
> Manufacturing Engineer
>
> AGM Container Controls
> 3526 E Ft. Lowell Road
> Tucson AZ, 85716
> PH: 520-881-2130
> FX: 520-881-4983
> www.AGMcontainer.com<http://www.agmcontainer.com/>
>
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]
6
> If I have an EcoMtl record and it has PullAsAsmb =true
> I'd like to run a query and get that assembly's EcoMtl records... AKA going
> all the way down the levels, the problem that I am having is that EcoMtl
> does not give you the Revision of the Part so I can't query the table
> without that, how do i get what revision that EcoMtl is attached ot?

There's a GetDataSetForTree method on the BOMSearch BO that has a flag to blow-through the whole BOM. Maybe there's an equivalent in the EngWorkBench adapter.

Mark W.
7
Jose,

Are you querying the ecomtl table? It has revisionnum in it.. wouldn't you query by the parents revnum?

Rob Bucek
Production Control Manager
PH: (715) 284-5376 ext 311
Mobile: (715)896-0590
FAX: (715)284-4084
[Description: cid:1.234354861@...]<http://www.dsmfg.com/>
(Click the logo to view our site)<http://www.dsmfg.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Jose Gomez
Sent: Thursday, February 23, 2012 12:34 PM
To: Vantage
Subject: [Vantage] EcoMlt?



Hi All

If I have an EcoMtl record and it has PullAsAsmb =true
I'd like to run a query and get that assembly's EcoMtl records... AKA going
all the way down the levels, the problem that I am having is that EcoMtl
does not give you the Revision of the Part so I can't query the table
without that, how do i get what revision that EcoMtl is attached ot?

Thanks!~

*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: jose@...<mailto:jose%40josecgomez.com>
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
8
In drilling down into an indented BOM, I have always used ECORev and ECOMtl back and forth to get the most recent revision number of the material parts. Hope that helps.

Aimee

--- In vantage@yahoogroups.com, Rob Bucek <rbucek@...> wrote:
>
> Jose,
>
> Are you querying the ecomtl table? It has revisionnum in it.. wouldn't you query by the parents revnum?
>
> Rob Bucek
> Production Control Manager
> PH: (715) 284-5376 ext 311
> Mobile: (715)896-0590
> FAX: (715)284-4084
> [Description: cid:1.234354861@...]<http://www.dsmfg.com/>
> (Click the logo to view our site)<http://www.dsmfg.com/>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Jose Gomez
> Sent: Thursday, February 23, 2012 12:34 PM
> To: Vantage
> Subject: [Vantage] EcoMlt?
>
>
>
> Hi All
>
> If I have an EcoMtl record and it has PullAsAsmb =true
> I'd like to run a query and get that assembly's EcoMtl records... AKA going
> all the way down the levels, the problem that I am having is that EcoMtl
> does not give you the Revision of the Part so I can't query the table
> without that, how do i get what revision that EcoMtl is attached ot?
>
> Thanks!~
>
> *Jose C Gomez*
> *Software Engineer*
> *
> *
> *checkout my new blog <http://www.usdoingstuff.com> *
> *
> *T: 904.469.1524 mobile
> E: jose@...<mailto:jose%40josecgomez.com>
> http://www.josecgomez.com
> <http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
> <http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
> <http://www.josecgomez.com/professional-resume/>
> <http://www.josecgomez.com/feed/>
> <http://www.usdoingstuff.com>
>
> *Quis custodiet ipsos custodes?*
>
> [Non-text portions of this message have been removed]
>
>
>
> [Non-text portions of this message have been removed]
>