Updating Expiry Dates to the Future from JDE Default 12/31/2010

We have effectively tracked down the Data Dictionary default settings for defaulting expiration dates. These are set in CENTCHG and DCYR. We then effectively pushed out the default from expiration dates of 12/31/2010 to 12/31/2030 for newly entered data records.

The issue we then had to hurdle was how to identify tables and fields that needed to be extended to the new expiry date via SQL scripting and manual inspection. We did this by utilizing the data dictionary (F9210) and XREF (F980021) tables in JDE. First, we looked for DATE data types in the DD that had key phrases in them like "END", "THRU", and "EXPIR". Once we found this list of data fields we queried against the XREF tables looking for tables that utilized these data fields. Next, we generated queries that would inspect these fields in these tables for dates hard coded to the old expiry date of 12/31/2010 (110365). Queries where we found at least 1 record with a 12/31/2010 date in them were flagged as potential tables we needed to look at. Of course, we also found files that should have this date in it like fiscal date patterns and such that we would leave alone.

The next step was to review these records flagged as truly expiry dates and generate SQL scripts to effectively update these fields to our new default expiry date of 12/31/2030 from 12/31/2010. This part we have almost completed as far as we can tell. We came up with roughly 25+ files that we had to review and/or create scripts for.

Has anyone come up with a better solution or completed this exercise that would be willing to share their approach. We would like to double check to make sure we didn't miss anything.

Thanks,

One thing to watch... If you are running UBEs in CSV mode and wish to open the CSV file directly in Excel, a 2030 date is automatically interpreted as 1930 unless you have the user setup to always use 4-digit year formatting. This is related to Microsoft bulletin...

http://support.microsoft.com/kb/214391

If you choose an expiration date of 12/31/2029 instead you will not see this problem.

I forgot to mention I found a solution for the export to Excel and CSV output to Excel by changing the OneWorld -> User Profile Revisions -> Date Format to a date format utilizing 4-digit Years (DME, EMD, MDE). This resolved my issue with Excel in all instances. Only drawback was dates are ALWAYS 4-digit now in OneWorld which cramped some tight reports that barely had enough room to start with when the dates were 2-digit year. The good thing is, it is a user specific option so it does not affect everyone immediately. You can try it out for yourself first and then make suggestions.

Hi,

First of all, Thanks for your detailed message. We also pushed our default expiry date from 2010 to 2020. Can you please mention the tables which you have updated to the latest expiry date, so that we can also cross check.

We had to update the Dates in the Receipts table (F03B13) for the post dated cheque dates. (by adding the julian date 100000 to the Date field DMTJ thru the SQL script.)

One doubt, now for dates above 31/12/10 is displayed as 01/01/2011, 01/01/2012, ... in the (MDE format) and below is in the MDY format. Our user Profiles are set for MDY format. Any suggestion to bring in MDY format for 2011, 2012 etc...

Thanks for all the information you shared. Yes, could you please share the list of tables you've updated. We're in the process of updating our expiration dates as well.

Thanks!

Tables listed at time we went through this process:

Table.Field Part of Primary Key
F3002.IXEFFT
F3003.IREFFT
F3013.OTEXDJ
F4015.OTEXDJ
F4016.TUEXDJ
F40305.RMEXDJ Y
F40306.SREXDJ Y
F40309.DYEXDJ Y
F40314.PJEXDJ Y
F40316.OPEXDJ Y
F40341.NSEXDJ Y
F4104.IVEXDJ Y
F4106.BPEXDJ Y
F41061.CBEXDJ Y
F42004.CMCXPJ Y
F43090.PCCXPJ
F4950.RTEXDJ
F4956.IXEXDJ
F4972.RXEXDJ

Other notes added:
Where the Field in question is part of the Primary Unique Key, steps will have to be taken to be sure that another record doesn’t already exist with the 2030 date in the place of the 2010 date and the remainder of the primary key. Also, we need to confirm that new dates have not been added for 1/1/2011 and beyond that we would be effectively extending records that the users may not want to extend.

My Oracle Support has published documentation and fixes:

For EnterpriseOne (EnterpriseOne Solution):
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=882478.1


For WorldSoftware (WorldSoftware Solution):
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=656831.1

Changing the century date will not fix the issue where 1/1/2040 is interpreted as 1/1/1940 when UBE is exported to Excel. Oracle says that it's because of Microsoft 2029 issue.

This is a simple fix in Windows. Go to Control Panel -> Clock, Language, Region -> Region and Language -> Additional Settings -> Date tab. Change the year under the Calendar section where it talks about two digit years. We changed them to 1970 - 2069. So, any two digit year is interpreted as being in this range. To make it easier, we put this setting into the Domain Group Policies so all PCs automatically use this date range.

Just tested this yesterday - the Control Panel change works great to fix date formats that users type into Excel manually. Unfortunately, it doesn't fix the issue when you're exporting E1 data to Excel. See https://support.oracle.com/epmos/faces/DocumentDisplay?id=1281607.1 which refers to http://support.microsoft.com/?id=230931

-Jim