In JD
Edwards EnterpriseOne applications release 9.0 (without ESU JL61235 applied), CRM
case history is not time stamped correctly. The manifestation of the problem is that case history is incorrect and
does not accurately reflect when cases were updated, which can be very
important if you are tracking cases to SLAs.
To
understand the issue we need to understand how information is held in the
F1755. The primary keys on the table
are:
- ZASTAW
(status) – is set to 2 for the active record and 1 for historical record
- ZAUPMJ
(Date – Updated)
- ZAUPMT
(Time – Updated)
- ZADOCO
(Document Number)
Data for a
particular case is held as follows:
- When
a CRM case is created, a new record is created in the F1755 table with a new
document number assigned (ZADOCO), date and time stamped with current time (ZAUPMJ
and ZAUPMT) and status (ZASTAW) is set to 2.
- When the record is updated, the existing record should just be changed to STAW = 1
and write the new line created with ZASTAW = 2 an updated current time in ZAUPMJ
and ZAUPMT.
Simple
really, but this is not the case without ESU JL61235 applied. Prior to this ESU the record updates
incorrectly by setting ZASTAW to 1 and the ZAUPMJ / and ZAUPMT to current date
/ time. Therefore it looks like the
historical record was written today and tracking cases and status changes
(ZACLST) is meaningless to the user.
ESU JL61235
fixes the problem with new P90CG50x applications and associated business
functions. There are no Special Instructions
listed for the ESU. When I applied the
ESU and tested the functionality it worked perfectly for new cases. However when updating existing cases I received
an error:
CAUSE: ERROR: File
can not be accessed.
Solution. . . Change
IBM security or authority using the IBM EDTOBJAUT command.
This file can not be
accessed with the current IBM authority
for the User
executing this request.
This occurs
because there are (old) invalid records which cause a primary key violation
when updating the case.
For example,
if I create a case on January 1 and update it on January 2. Without the ESU it time stamps the original
and new record as January 2 (and identical times) and therefore the active and original
records’ primary keys are identical with the exception of ZASTAW for all
existing cases in the database with at least one historical record. When the case is updated (correctly with the
applied ESU) JDE tries to change the current record to a historical record (setting
ZASTAW to 1 and not re-stamping dates and times) and hence the violation.
To work
around this issue I need to adjust the records so that ZASTAW is not the only
difference. But I have no way of knowing
what the existing historical record (ZASTAW = 1) should be therefore as a work
around I subtract one second from the records (assuming it is not midnight)
using the following SQL:
update F1755 o
set o.ZAUPMT = (o.ZAUPMT - 1)
where o.ZASTAW = '1' and exists (select *
from F1755 i
where i.ZADOCO = o.ZADOCO and i.ZAUPMJ = o.ZAUPMJ and i.ZAUPMT =
o.ZAUPMT and i.ZASTAW = '2') and o.ZAUPMT != 0
If you are
doing this, you probably want to check for records that are at midnight (ZAUPMT
= 0) and add a few seconds to the active record rather than worrying about a changing
the date as the information is wrong anyway.
Tip: F1755
records are time stamped adjusted for your time zone. So even if you don’t work 24 hour shifts, the
ZAUPMT could be 0 depending on your time zone.
Struan Hijner, Infrastructure Services Practice Lead
Myriad IT
03-8530-8600