000035563 - TERMINATION_DATE from Oracle HRMS Authentication System is not getting stored in RSA Identity Governance and Lifecycle 7.0

Document created by RSA Customer Support Employee on Oct 18, 2017
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000035563
Applies ToRSA Product Set: Identity Governance and Lifecycle
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.0
IssueFor 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.
CauseIn 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.
ResolutionIn 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.
NotesFor 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.