So there is an error in one of the programs that has been running fine so far in our Production iSeries.
Statement in Error . . . . . . . . . . : 00051900
RPG Routine . . . . . . . . . . . . . : REPROMIS
Number of Parameters . . . . . . . . . : 005
Message Type . . . . . . . . . . . . . : RNX
Additional Message Info . . . . . . . : 0114
Message Data . . . . . . . . . . . . . :
The year portion of a Date or Timestamp value is not in the correct range.
Above is the error. So i understand that the Statement in error is 519 which is this
RepromiseMode();
So this is just a procedure being called in the iSeries program. As I have mentioned in one of my earlier posts, I am still learning how to read spool files. I have gone through the Job logs, and below is something which I see which might be relevant I thought:
Message . . . . : Data mapping error on member FG205L01.
Cause . . . . . : A data mapping error occurred on field
Max(FG205F_1.QSRDTE) in record number 0, record format *FIRST, member number
1, in member FG205L01 file FG205L01 in library FILES, because of error code
18. The error code meanings follow: 1 -- There is data in a decimal field
that is not valid. 2 -- A significant digit was truncated. 3 -- A floating
point value exceeded the maximum representable value. 4 -- A floating point
value was less than the minimum representable value. 5 -- A binary floating
As the above message says, error code 18 is :
18 -- There is data in a date, time, or timestamp field that is not valid.
Now a bit puzzled on how to move forward. Would this just be a data issue or would this be a program related issue that needs a code change?
Below is the dropbox link where I have placed the spool file.
https://www.dropbox.com/s/1jbgqvqn9zaac8g/PD001R_Error.txt?dl=0
Any guidance on how to move forward would be helpful
**
Update - 1
So as far as the research I did so far taught me, i need to concentrate on the procedure below
P RepromiseMode B
// ...... Define the procedure interface
D RepromiseMode PI
/Free
//- Spin the whole file
CallP PW012R('INFO':'PD001R0055':ModuleName:' ':
' ':' ':' ':'0':' ':P_COMOrd:P_ItmSeq:
' ':' ':' ':' ':' ');
If P_COMOrd = *Blanks;
Setll (*Loval) PD002F;
Read PD002F;
Else;
If P_ItmSeq = *Zeros;
Setll (1:'1':P_COMOrd) PD002F;
Reade (1:'1':P_COMOrd) PD002F;
Else;
Setll (1:'1':P_COMOrd:%Dec(P_ItmSeq:7:0)) PD002F;
Reade (1:'1':P_COMOrd:%Dec(P_ItmSeq:7:0)) PD002F;
EndIf;
EndIf;
Dow Not %Eof(PD002F);
wOrdCmp = E_A2AENB;
wOrdTyp = E_A2DCCD;
wOrdNbr = E_A2CVNB;
wItmSeq = E_A2FCNB;
wModel = E_A2MODL;
hldInvDt = E_A2INVD;
hldBldDt = E_A2BLDA;
hldQuaDt = LowDate;
hldTrnDt = LowDate;
hldShpDt = LowDate;
hldPrmDt = LowDate;
MoreDates = *Off;
GAC03 NextRecord = *Off;
GAC11 //- Check if it is the correct repromise
GAC11 CorrectOrd = *Off;
GAC11 Exec Sql
GAC11 Select '1' into :CorrectOrd
GAC11 From mbc6res0
GAC11 Inner Join pd001f On a1ordy=c6uucc And a1adr0=c6uuca
GAC11 where c6aenb=:wOrdCmp And c6dccd=:wOrdTyp
GAC11 And c6cvnb=:wOrdNbr And a1dvnm=:ModuleName;
GAC11 If SqlCode > 100;
GAC11 // Actually nothing yet if error then it will not run past here
GAC11 EndIf;
GAC11 //- If the record is for this repromise continue
GAC11 If CorrectOrd;
//- Conitune if the PD002F is written
GetInvDate();
If PD002Good;
Chain (wOrdCmp:wOrdTyp:wOrdNbr:wItmSeq) FG263L02;
If %Found(FG263L02);
Chain (K_FGVIN#) FG203F;
If %Found(FG203F);
If hldBldDt > *Loval;
wInventoryDt = hldBldDt;
Else;
wInventoryDt = %Date();
EndIf;
//- Shippable is Yes
If V_SHIP = 'Y';
wFinGood = 'Y';
wSalFmy = V_SFMLY;
wPline = V_PLINE;
wHouse = V_CHOUSE;
ProcRePromise();
GetPD002F();
//- Shippable is NO
Else;
CallP PW012R('INFO':'PD001R0090':ModuleName:V_CHOUSE:
V_VIN:V_MODEL:' ':%Char(V_ORDCMP):V_ORDTYP:
V_ORDNBR:'0':' ':' ':' ':' ':' ');
LoopDone = *On;
wFinGood = 'Y';
wSalFmy = V_SFMLY;
wPline = V_PLINE;
wHouse = V_CHOUSE;
ProcRepromise();
GetPD002F();
EndIf;
GAC03 //- Some how there is a valid vin in FG263F but not FG203F then...
GAC03 Else;
GAC04 CallP PW012R('INFO':'PD001R0210':ModuleName:' ':
GAC04 K_FGVIN#:' ':' ':%Char(wOrdCmp):wOrdTyp:
GAC04 wOrdNbr:%Char(wItmSeq):' ':' ':' ':' ':' ');
GAC03 SendEmail = *On;
GAC04 GetPD009F();
EndIf;
//- No VIN was found
Else;
Chain (wOrdCmp:wOrdtyp:wOrdNbr:wItmSeq) PW019L01;
If %Found(PW019L01);
//- Found in PW019F and it is a Finshed Good VIN
If N_B0FASG = 'Y';
//- End the process the order has not been assigned in the warehouse
// yet, but there is a label waiting for assignment all is good.
CallP PW012R('INFO':'PD001R0185':ModuleName:' ':
' ':wModel:' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:
%Char(wItmSeq):' ':' ':' ':' ':' ');
Else;
//- Looks for a slot record
Chain (wOrdCmp:wOrdtyp:wOrdNbr:wItmSeq) PW014F;
If %Found(PW014F);
hldBldDt = P_B5PDAT;
MoreDates = *On;
//- No slot record look for a MO order
Else;
Chain (wOrdCmp:wOrdtyp:wOrdNbr:wItmSeq) MOMASTLG;
If %Found(MOMASTLG);
hldBldDt = %Date(M_ODDTMY:*CYMD);
MoreDates = *On;
//- No MO record send an error email
Else;
CallP PW012R('INFO':'PD001R0170':ModuleName:' ':
' ':wModel:' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:
%Char(wItmSeq):' ':' ':' ':' ':' ');
SendEmail = *On;
GAC04 GetPD009F();
EndIf;
EndIf;
EndIf;
//- Error because not in PW019F
Else;
CallP PW012R('INFO':'PD001R0175':ModuleName:' ':
' ':wModel:' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:
%Char(wItmSeq):' ':' ':' ':' ':' ');
SendEmail = *On;
GAC04 GetPD009F();
EndIf;
//- If there needs to be more dates calculate
If MoreDates;
GetSalesFamly();
P_NumDays = *Zeros;
wModel = E_A2MODL;
CallP PD005R(wSalFmy:' ':wModel:P_NumDays);
DecNumDays = %Dec(P_NumDays:4:0);
If DecNumDays = *Hival;
CallP PW012R('INFO':'PD001RE025':ModuleName:' ':
' ':wModel:' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:
%Char(wItmSeq):'PD005R came back with 9999 days':
' ':' ':' ':' ');
SendEmail = *On;
NextRecord = *On;
GetPD009F();
Else;
Chain (1:'1':wOrdNbr:wItmSeq) MBCDRES0;
If %Found(MBCDRES0);
wHouse = B_CDA3CD;
Chain (1:'1':wOrdNbr:wItmSeq) MOMASTLG;
If %Found(MOMASTLG);
Chain (M_FIWHMY) WHSMST;
If %Found(WHSMST);
//- Move forward number of days agains work cacendar
CYMDDate = %Dec(%Char(hldBldDt:*CYMD0):7:0);
Setll (W_CALP:CYMDDate) CALNDRL0;
For j = 0 to DecNumDays;
Reade (W_CALP) CALNDRL0;
CYMDDate = CDTECA;
EndFor;
EndIf;
//- Now update the Quality Date
hldBldDt = %Date(CYMDDate:*CYMD);
wInventoryDt = hldBldDt;
CallP PW012R('INFO':'PD001R0125':ModuleName:' ':
' ':wModel:' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:
%Char(wItmSeq):' ':' ':' ':' ':' ');
GetPD002F();
EndIf;
GAC05 Else;
GAC05 // DO not send email on this because the file is empty
GAC05 CallP PW012R('INFO':'PD001R0230':ModuleName:wHouse:
GAC05 ' ':' ':' ':'0':' ':wOrdNbr:%Char(wItmSeq):
GAC05 ' ':' ':' ':' ':' ');
GAC05 NextRecord = *On;
EndIf;
//- Get all the dates needed
wFinGood = 'N';
If Not NextRecord;
QualityDate();
CallP PW012R('INFO':'PD001R0140':ModuleName:' ':' ':wModel:
' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:%Char(wItmSeq):
' ':' ':' ':' ':' ');
EndIf;
If Not NextRecord;
TransferDate();
CallP PW012R('INFO':'PD001R0122':ModuleName:' ':' ':wModel:
' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:%Char(wItmSeq):
' ':' ':' ':' ':' ');
EndIf;
If Not NextRecord;
ShipWedgeDate();
CallP PW012R('INFO':'PD001R0123':ModuleName:' ':' ':wModel:
' ':%Char(wOrdCmp):wOrdtyp:wOrdNbr:%Char(wItmSeq):
' ':' ':' ':' ':' ');
GetPD002F();
EndIf;
EndIf;
EndIf;
EndIf;
EndIf;
GAC11 EndIf;
If P_COMOrd = *Blanks;
Read PD002F;
Else;
If P_ItmSeq = *Zeros;
Reade (1:'1':P_COMOrd) PD002F;
Else;
Reade (1:'1':P_COMOrd:%Dec(P_ItmSeq:7:0)) PD002F;
EndIf;
EndIf;
EndDo;
/End-Free
P RepromiseMode E
I am debugging this at the moment but it hasn't crashed so far to pinpoint which date assignment/date related operation is causing the issue.
UPDATE - 2
In the procedure above, a file PD002F is being read. To see the date values of all fields having date data type, I tried to extract this file to Excel using the Export option in CLient access but then the same error was thrown:
CWBDB0099 - No more data is available for the stream fetch request
SQL0181 - Value in date, time, or timestamp string not valid.
Cause . . . . . : The string representation of a date, time or timestamp value is not in the acceptable range. *N is either the character string constant that is not valid or the column or host variable that contained the string.
So is the file corrupt or something?
UPDATE -3
I tried to extract just one record using RRN < 2 through client access and it worked fine! But when i gave RRN < 3, the same error was thrown. This further strengthens my suspicion that this issue comes up only for particular records.
Update 4
Here is the offending data from the dump, and the associated date operations:
hldInvDt = E_A2INVD;
hldBldDt = E_A2BLDA;
hldQuaDt = LowDate;
hldTrnDt = LowDate;
hldShpDt = LowDate;
hldPrmDt = LowDate;
wInventoryDt = hldBldDt;
wInventoryDt = %Date();
hldBldDt = P_B5PDAT;
hldBldDt = %Date(M_ODDTMY:*CYMD);
CYMDDate = %Dec(%Char(hldBldDt:*CYMD0):7:0);
hldBldDt = %Date(M_ODDTMY:*CYMD);
CYMDDate = %Dec(%Char(hldBldDt:*CYMD0):7:0);
CYMDDate = CDTECA;
hldBldDt = %Date(CYMDDate:*CYMD);
HLDINVDT DATE(10) '2017-02-10'
E_A2INVD DATE(10) '2017-02-10'
LOWDATE DATE(10) '0001-01-01'
HLDQUADT DATE(10) '0001-01-01'
HLDTRNDT DATE(10) '0001-01-01'
HLDSHPDT DATE(10) '0001-01-01'
HLDPRMDT DATE(10) '0001-01-01'
WINVENTORYDT DATE(10) '2017-02-10'
P_B5PDAT DATE(10) '0017-02-10'
WINVENTORYDT DATE(10) '2017-02-10'
HLDBLDDT DATE(10) '0017-02-10'
M_ODDTMY PACKED(7,0) 1170210.
CYMDDATE ZONED(7,0) 1170210
CDTECA PACKED(7,0) 1170126
There are multiple date formats used by IBM i within a date field. They hold different date ranges based on the format of the year portion of the date.
Number of Digits in Year Range of Years 2 (*YMD, *DMY, *MDY, *JUL) 1940 to 2039 3 (*CYMD, *CDMY, *CMDY) 1900 to 2899 4 (*ISO, *USA, *EUR, *JIS, *LONGJUL) 0001 to 9999
The error you received indicates that you attempted to move a date with a 4 digit year to a date with a 2 or 3 digit year, and that the year value was out of range for the target date. In this case the specific offender appears to be hldBldDt which at the time of the error hade a year of 0017, and is presumably in *ISO format. there are a couple places you do this:
CYMDDate = %Dec(%Char(hldBldDt:*CYMD0):7:0);
I suspect one of these triggered your error since the year in hldBldDt is 0017 and for *CYMD that is out of range (the allowable range for *CYMD is 1900 - 2899). The question is where did that come from since hldBldDt is usually populated using *CYMD, and that should keep the dates in range. Looking at the data, I see that P_B5PDAT also has a year of 0017, and that you are assigning P_B5PDAT directly to hldBldDt. So if this has been running fine, either the value in P_B5PDAT has not been used, or somehow you got a bad year in there.