|Applies To||RSA Product Set: Identity Governance and Lifecycle|
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.0
|Issue||For an Identity Collection from an Oracle HRMS system, for some dates, the data is not getting mapped to the RSA TERMINATION_DATE attribute.|
The source Oracle HRMS column EMP_TERM_DATE is of type VARCHAR.
The target RSA attribute TERMINATION_DATE is of type DATE.
Therefore, for the user data query, the SELECT must use the TO_DATE function to convert the EMP_TERM_DATE, for example TO_DATE(EMP_TERM_DATE, 'YYYY-MM-DD HH:MI:SS').
However, even when this is done, the date is still not captured in the RSA attribute TERMINATION_DATE.
|Cause||In some circumstances, the Oracle HRMS column EMP_TERM_DATE may contain the following data "4712-12-31 12:00:00.0".|
This is a Julian date, so it needs to be handled differently to a a normal Gregorian Date.
Further, Julian "day zero" is January 1, 4713 BC, so 4712-12-31 is the day before that, where this means it is the equivalent of a NULL date.
This value can also be seen in the Raw Data being collected.
|Resolution||In the case where the Oracle HRMS column EMP_TERM_DATE has the value "4712-12-31 12:00:00.0", then having no data in the RSA attribute TERMINATION_DATE is expected behavior.|
This is because the Julian date value "4712-12-31 12:00:00.0" is equivalent to a NULL or empty date.
|Notes||For more information on how the Oracle database handles dates, please review Doc ID 69028.1 - How does Oracle store the DATE datatype internally?, where you will need to login to the Oracle Support portal.|
Alternately, you can access the Oracle online documentation for the DATE Data Type.