Re: Date Math 405 CD 5.1

I have a SQL function (shown below) which doesn't solve your requirement
but you may find it useful.

This function allows, for example, the number of FSC defined working
days (ignore 'W'eekend, 'H'oliday, 'S'hutdown) to be calculated between
two dates. I know there are potential issues (doesn't look at facility
or work centre) but this works with our data and for our needs for now.

I use this to calculate the number of FSC working days between the shop
order entered date and the (calculated) shop order close date, which can
then be compared against the item lead time.

Select workdays(dec2date(SOENDT),FSOCLSDT(SORD),'WSH') from FSO

Where SOENDT = 20110101 and calculated close date is 20110110 would give
6 working days with our calendar.

CREATE FUNCTION lib/WORKDAYS (
FDATE DATE ,
LDATE DATE ,
VTYPE VARCHAR(5) )
RETURNS DECIMAL(5, 0)
LANGUAGE SQL
SPECIFIC lib/WORKDAYS
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE AT_END INT DEFAULT 0 ;
DECLARE RES VARCHAR ( 32565 ) ;
DECLARE ONEYEAR CHAR ( 366 ) ;
DECLARE C1 CURSOR FOR SELECT SCTYPE FROM FSC WHERE YEAR ( DIGITS (
SCYEAR ) CONCAT '-01' CONCAT '-01' ) BETWEEN YEAR ( FDATE ) AND YEAR (
LDATE ) ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 1 ;
SET RES = '' ;
OPEN C1 ;
FETCH C1 INTO ONEYEAR ;
WHILE AT_END = 0 DO
SET RES = RES CONCAT RTRIM ( ONEYEAR ) ;
FETCH C1 INTO ONEYEAR ;
END WHILE ;
CLOSE C1 ;
IF TRIM ( VTYPE ) = '' THEN SET VTYPE = 'W' ; END IF ;
-- TRANSLATE(res, '*', vType) not possible :(
IF LOCATE ( 'W' , VTYPE ) > 0 THEN SET RES = TRANSLATE ( RES , '*' , 'W'
) ; END IF ;
IF LOCATE ( 'A' , VTYPE ) > 0 THEN SET RES = TRANSLATE ( RES , '*' , 'A'
) ; END IF ;
IF LOCATE ( 'S' , VTYPE ) > 0 THEN SET RES = TRANSLATE ( RES , '*' , 'S'
) ; END IF ;
IF LOCATE ( 'H' , VTYPE ) > 0 THEN SET RES = TRANSLATE ( RES , '*' , 'H'
) ; END IF ;
RETURN LENGTH ( REPLACE ( SUBSTRING ( RES , DAYOFYEAR ( FDATE ) , DAYS (
LDATE ) - DAYS ( FDATE ) + 1 ) , '*' , '' ) ) ;
END ;



-----Original Message-----
From: bpcs-l-bounces@xxxxxxxxxxxx [mailto:bpcs-l-bounces@xxxxxxxxxxxx]
On Behalf Of Al
Sent: 03 June 2011 20:37
To: 'BPCS ERP System'
Subject: [BPCS-L] Date Math 405 CD 5.1

I have been requested to do a modification, and I am having difficulty
locating good tools to support it. We are BPCS 405 CD, IBM 5.1.
* Is there a standard subroutine, like in QSTD, or native IBM,
where
we feed in date and it returns if that Date is a Monday Wednesday or
whatever day of week? This is because they want something to happen
based on business days, not calendar days.
* Is there a standard subroutine, where I can feed in some date
from
some BPCS data, and some # of business days (3 days is their current
interest, but that could change), access the FSC shop calendar, and
figure that out ... like if the BPCS date is for a Monday, then 3
business days prior to that is the previous Wednesday.

-
Al Mac

Assuming no one has any objections, I plan to be deleting work e-mail
which is over a year old, because my e-mail is getting sluggish.