SQL Logic to Select Current PartRev in a BOM

system · August 7, 2008, 4:52am

I tried this with a BAQ and it works as intended. The query will bring me the last revision of the current part. The BAQ is below. I made some selections in it to keep from querying the complete Part Master table. The begin SO selects our sold top level units only and because we have alternate methods I excluded them in the BAQ. I left the sort in ascending order and chose the last part revision. Also, it appears we still have to get around the TODAY thing by putting a value in the From Today Value in the BAQ, i.e. you can't just select the Use From Today Value.

for each Part where ( Part.PartNum BEGINS 'SO') no-lock , last PartRev where ( PartRev.EffectiveDate <= TODAY + 0 AND PartRev.AltMethod <> 'Knockdown') and (Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum) no-lock by Part.Company by Part.PartNum by PartRev.EffectiveDate.

I checked the data and it does appear to only bring me the last revision. I also added a revision to one part with an effective date in the future and the BAQ again did as intended and gave me the revision prior to the one I added.

Also, in looking at your SQL statement should it not say SELECT TOP 1 effectivedate, revisionnum, desc instead of SELECT TOP 1 revisionnum. I didn't notice that earlier. Again, I am not an SQL guru so I could be totally wrong.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Brian W, Spolarich
Sent: Wednesday, August 06, 2008 9:29 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] SQL Logic to Select Current PartRev in a BOM

ThatÂ¹s what IÂ¹m doing, and IÂ¹m not getting the results I expect. We added
another revision (Â³UNRÂ²) that has an effective date after the original ones
we loaded, and the BOMs show the initial Â³AÂ² rev instead of the Â³UNRÂ². I
feel like IÂ¹m missing something essential in understanding the BOM/PartRev
relationship.

-bws

On 8/6/08 6:47 PM, "Charles Carden" <ccarden@...<mailto:ccarden%40manitex.com>> wrote:

>
>
>
> If you are looking for only current and not some effective date in the past,
> the selection you have should work fine if you sort the revision in descending
> order by date and then take only the 1st record you read. If you are not
> reading records then read only the max(effective date) record. It has been
> awhile since I have used SQL so please excuse me if I have the syntax all
> wrong.
>
> 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 Brian W. Spolarich
> Sent: Wednesday, August 06, 2008 3:12 PM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] SQL Logic to Select Current PartRev in a BOM
>
> How does one select the current PartRev associated with a Part for a
> BOM?
>
> That is to say, what is the logic that Vantage uses to determine which
> revision of a Part appears on a BOM? Each PartMtl record references a
> parent PartNum and RevisionNum, but the MtlPartNum does not reference a
> particular revision.
>
> I thought that the "most recently effective and approved revision" would
> be the one that shows up in the BOM, but that doesn't seem to be the
> case.
>
> Is there anyplace other than PartRev and PartMtl that one needs to look
> to determine the structure of a BOM and the revisions included?
>
> This logic doesn't seem to work:
> SELECT TOP 1 revisionnum from dbo.partrev
> where
> partnum = @PARTNUM and
> partrev.effectivedate <= getdate() and
> approved = 1 and
> company = @COMPANY
> order by effectivedate desc;
>
> [Non-text portions of this message have been removed]
>
> ________________________________
> DISCLAIMER:
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity to which
> it is addressed. If the reader of this e-mail is not the intended recipient or
> his or her authorized agent, the reader is hereby notified that any
> dissemination, distribution or copying of this e-mail is prohibited. If you
> have received this e-mail in error, please notify the sender by replying to
> this message and delete this e-mail immediately.
>
> [Non-text portions of this message have been removed]
>
>
>

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

________________________________
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.

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

system · August 6, 2008, 4:12pm

How does one select the current PartRev associated with a Part for a
BOM?

That is to say, what is the logic that Vantage uses to determine which
revision of a Part appears on a BOM? Each PartMtl record references a
parent PartNum and RevisionNum, but the MtlPartNum does not reference a
particular revision.

I thought that the "most recently effective and approved revision" would
be the one that shows up in the BOM, but that doesn't seem to be the
case.

Is there anyplace other than PartRev and PartMtl that one needs to look
to determine the structure of a BOM and the revisions included?

This logic doesn't seem to work:
SELECT TOP 1 revisionnum from dbo.partrev
where
partnum = @PARTNUM and
partrev.effectivedate <= getdate() and
approved = 1 and
company = @COMPANY
order by effectivedate desc;

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

system · August 6, 2008, 6:46pm

If you are looking for only current and not some effective date in the past, the selection you have should work fine if you sort the revision in descending order by date and then take only the 1st record you read. If you are not reading records then read only the max(effective date) record. It has been awhile since I have used SQL so please excuse me if I have the syntax all wrong.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Brian W. Spolarich
Sent: Wednesday, August 06, 2008 3:12 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL Logic to Select Current PartRev in a BOM

How does one select the current PartRev associated with a Part for a
BOM?

That is to say, what is the logic that Vantage uses to determine which
revision of a Part appears on a BOM? Each PartMtl record references a
parent PartNum and RevisionNum, but the MtlPartNum does not reference a
particular revision.

I thought that the "most recently effective and approved revision" would
be the one that shows up in the BOM, but that doesn't seem to be the
case.

Is there anyplace other than PartRev and PartMtl that one needs to look
to determine the structure of a BOM and the revisions included?

This logic doesn't seem to work:
SELECT TOP 1 revisionnum from dbo.partrev
where
partnum = @PARTNUM and
partrev.effectivedate <= getdate() and
approved = 1 and
company = @COMPANY
order by effectivedate desc;

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

________________________________
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.

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

system · August 6, 2008, 10:28pm

ThatÂ¹s what IÂ¹m doing, and IÂ¹m not getting the results I expect. We added
another revision (Â³UNRÂ²) that has an effective date after the original ones
we loaded, and the BOMs show the initial Â³AÂ² rev instead of the Â³UNRÂ². I
feel like IÂ¹m missing something essential in understanding the BOM/PartRev
relationship.

-bws

On 8/6/08 6:47 PM, "Charles Carden" <ccarden@...> wrote:

>
>
>
> If you are looking for only current and not some effective date in the past,
> the selection you have should work fine if you sort the revision in descending
> order by date and then take only the 1st record you read. If you are not
> reading records then read only the max(effective date) record. It has been
> awhile since I have used SQL so please excuse me if I have the syntax all
> wrong.
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf
> Of Brian W. Spolarich
> Sent: Wednesday, August 06, 2008 3:12 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] SQL Logic to Select Current PartRev in a BOM
>
> How does one select the current PartRev associated with a Part for a
> BOM?
>
> That is to say, what is the logic that Vantage uses to determine which
> revision of a Part appears on a BOM? Each PartMtl record references a
> parent PartNum and RevisionNum, but the MtlPartNum does not reference a
> particular revision.
>
> I thought that the "most recently effective and approved revision" would
> be the one that shows up in the BOM, but that doesn't seem to be the
> case.
>
> Is there anyplace other than PartRev and PartMtl that one needs to look
> to determine the structure of a BOM and the revisions included?
>
> This logic doesn't seem to work:
> SELECT TOP 1 revisionnum from dbo.partrev
> where
> partnum = @PARTNUM and
> partrev.effectivedate <= getdate() and
> approved = 1 and
> company = @COMPANY
> order by effectivedate desc;
>
> [Non-text portions of this message have been removed]
>
> ________________________________
> DISCLAIMER:
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity to which
> it is addressed. If the reader of this e-mail is not the intended recipient or
> his or her authorized agent, the reader is hereby notified that any
> dissemination, distribution or copying of this e-mail is prohibited. If you
> have received this e-mail in error, please notify the sender by replying to
> this message and delete this e-mail immediately.
>
> [Non-text portions of this message have been removed]
>
>
>

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