Mike,
If you have odbc, you may find Brian's SQL will work on Progress with little or no editing.
(Worth a try.)
Rob
If you have odbc, you may find Brian's SQL will work on Progress with little or no editing.
(Worth a try.)
Rob
--- On Tue, 11/25/08, micks@... <micks@...> wrote:
From: micks@... <micks@...>
Subject: RE: [Vantage] Revision Notification on a PO
To: vantage@yahoogroups.com
Date: Tuesday, November 25, 2008, 1:25 PM
I am using Progress but will hang on to your code. We've talked about
switching to SQL down the road.
Thanks for all your help.
Mike Icks
IT Department
Milltronics Manufacturing
x41434
(Direct)
micks@milltronics. net
From:
"Brian W. Spolarich " <bspolarich@advanced photonix. com>
To:
<vantage@yahoogroups .com>
Date:
11/24/:50 PM
Subject:
RE: [Vantage] Revision Notification on a PO
I use this logic in MSSQL to get the current effective PartRev:
select top 1 revisionnum from dbo.partrev where
partnum = @PARTNUM and
partrev.effectiveda te <= getdate() and
partrev.approveddat e <= getdate() and
approved = 1
order by effectivedate desc
I wrapped this in a User-Defined Function in MSSQL (see below) so I
could reference it inline in a SELECT statement, as in:
SELECT pm1.company, pm1.partnum, CONVERT(VARCHAR, REPLICATE( @INDENTCHAR, 1)
+ pm1.mtlpartnum) ,
dbo.API_funcGetCurr entPartRev( pm1.mtlpartnum, @COMPANY) ,p1.method,
CONVERT(VARCHAR( 1024),p1. partdescription) ,pm1.mtlseq, [. .
. and so on]
If you're using Progress DB and not MSSQL then this is of less value,
since I doubt Progress SQL supports the "TOP 1" modifier.
Since I'm returning a scalar value in the UDF, I don't have the do the
LIMIT 1 business and I sort by date in ascending order instead of
descending which gives me the last value in the result set as the return
value of the function.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix
bspolarich@advanced photonix. com ~ ~
www.advancedphotoni x.com
USE [MfgTest803]
GO
/****** Object: UserDefinedFunction [dbo].[API_funcGetC urrentPartRev]
Script Date: 11/24/:48:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============ ========= ========= ========= ======
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- ============ ========= ========= ========= ======
CREATE FUNCTION [dbo].[API_funcGetC urrentPartRev]
(
-- Add the parameters for the function here
@PARTNUM varchar(32), @COMPANY varchar(8)
)
RETURNS VARCHAR(8)
AS
BEGIN
-- Declare the return variable here
DECLARE @CURRENTREV VARCHAR(8);
SELECT @CURRENTREV = revisionnum from dbo.partrev where
partnum = @PARTNUM and
partrev.effectiveda te <= getdate() and
partrev.approveddat e <= getdate() and
approved = 1
order by effectivedate asc
-- Return the result of the function
RETURN @CURRENTREV
END
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of micks@milltronics. net
Sent: Monday, November 24, :00 PM
To: vantage@yahoogroups .com
Subject: [Vantage] Revision Notification on a PO
Has anyone created a PO document that lists the components to a
purchased
assembly and their current revision? We have job shops that fabricate
assemblies for us according to our specifications. We want to send them
a
PO for the purchase of an assembly and list the components they are to
use
to build it and their current rev. I'm able to use a Crystal sub-report
to list the components but am having a problem selecting only the latest
rev based upon effectivity date. I can't figure out a way to print only
the rev with the latest effectivity date, I get either all revs or no
revs.
Thanks,
Mike Icks
IT Department
Milltronics Manufacturing
x41434
(Direct)
micks@milltronics. net <mailto:micks% 40milltronics. net>
[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]