INFOR support has an "unsupported" re-sequencing program. I ran it this
past weekend with adverse results. I had planned to develop my own
routine, thanks for sharing yours! Looking at your IIM update script
answered another question for me related to the value of ITSEQ.
Several user had contacted out IT department with questions / concerns
related to Material Status Inquiry and inaccurate results in the History
Panel.
-----Original Message-----
From: bpcs-l-bounces+bwaters=mtolivepickles.com@xxxxxxxxxxxx
[mailto:bpcs-l-bounces+bwaters=mtolivepickles.com@xxxxxxxxxxxx] On
Behalf Of McGovern, Sean
Sent: Friday, February 19, 2010 3:22 AM
To: BPCS ERP System
Subject: Re: [BPCS-L] Seq# in ITH corrupted
If I recall correctly, the INV300 History screen display is in the TSEQ
order. So this display may be 'confused'. Also, I think that duplicate
sequence numbers could also cause looping in some programs.
Why not just correct the sequence number ? Looking at my list of saved
SQL scripts, I have this (UPDSEQ): -
Create View QTEMP/ITHVIEW as
(Select TTDTE, THTIME, TSEQ, rrn(ITH) as RN
From ITH
Where TPROD = 'XXX');
Update QTEMP/ITHVIEW a
Set a.TSEQ =
(Select -99999 + count(*)
From QTEMP/ITHVIEW b
Where (a.TTDTE>b.TTDTE)
Or (a.TTDTE=b.TTDTE and a.THTIME>b.THTIME)
Or (a.TTDTE=b.TTDTE and a.THTIME=b.THTIME and a.RN>b.RN));
Update IIM
Set ITSEQ =
(Select max(TSEQ)
From ITH
Where TPROD=IPROD)
Where IPROD = 'XXX';
I don't have time now to check that the above actually works correctly,
and it was written in 2006, so please test it first ! Also, for safety,
ensure no users are using BPCS at the time of the correction. Also, test
it on a cut-down version of your ITH file as it may be a long running
statement.
These SQL statements assumes BPCS V6 , were TSEQ is only 5 digits.
Please adjust if you at a different version. Also note that it starts
re-sequencing at -99999, not 0 or 1. Having a negative sequence number
still functions correctly throughout BPCS and allows twice as many ITH
records before you have to consider archiving.
Cheers,
Sean
-----Original Message-----
From: bpcs-l-bounces+sean.mcgovern=covidien.com@xxxxxxxxxxxx
[mailto:bpcs-l-bounces+sean.mcgovern=covidien.com@xxxxxxxxxxxx] On
Behalf Of Don Cavaiani
Sent: 18 February 2010 19:57
To: bpcs-l@xxxxxxxxxxxx
Subject: [BPCS-L] Seq# in ITH corrupted
I forget how important you have said it is to have the ITH seq #
correct??
I have managed to 'corrupt' the sequence # of several ITH records for
given part numbers!
Looking at ITH history chronologically, seq# is working, then randomly
reverts back to some already used sequence numbers (probably due to a
program mod of mine), then straightens itself out after that. I don't
think the SEQ# in IIM ever got corrupted, just certain transactions in
the ITH file ??? Other than viewing the ITH transaction which are not
"out of chronological sequence" because of the corruption, can anything
more major happen?
TIA
Don F. Cavaiani
IT Manager
Amerequip Corp.
920-894-7063
"It's amazing what you can accomplish if you don't care who gets the
credit." Harry S. Truman
midrange.com
