System Date Format error: the date of birth cannot be a future date

 

Problem description:

Personnel basic information ImportProgramIf the date of birth is before January 1, 1950, the system reports the following error:

An error occurred while creating basic employee information. Contact the system administrator.
Sqlcode is:-20001
Sqlerrm is: ORA-20001: The date of birth cannot be a future date.

Analysis:
After tracking, we found that the birth date was converted to 20XX years when it was imported to the system.
For example, after conversion from 1949-01-01 to 2049-01-01
The intermediate result is: Month 1-49.

Date_of_birth = trim (date_of_birth), -- the date of birth is an error caused by this function.

-- To_date ()

Date Format: XX-x month-xx lost the first two pieces of information of the year, and then changed to four years, it is based on the dividing line of xx50 years. Xx50 years ago, after conversion: 20XX, 50 after conversion to 19xx.

The date before January 1, is converted to January 20, and after January 1, is converted to 19xx.

Result: this is an error caused by the system date format.

Cas_hrms_interface_api.hr_change_session_date_format; this function can solve this problem. He adjusted the system time format to the standard format: dd-mm-yyyy.

Bytes ------------------------------------------------------------------------------------------------------

Confirm the problem is caused by the update hr_employee_interface statement or trim function. The solution is to comment out the update statement.

Update hr_employee_interface
Set
Hire_date = trim (hire_date), -- enter the unit time
Last_name = trim (last_name), -- name Chinese name
Sex = trim (sex), -- Gender
Person_type_id = trim (person_type_id), -- personnel type
National_identifier = trim (national_identifier), -- ID card
Date_of_birth = trim (date_of_birth), -- Date of birth
Town_of_birth = trim (town_of_birth), -- ancestral home

... ...

Attribute6 = trim (attribute6), -- allocate information_level
Attribute7 = trim (attribute7), -- allocate information_payroll
Attribute8 = trim (attribute8), -- Allocation information_status
Attribute9 = trim (attribute9), -- distribute information_employer
Attribute10 = trim (attribute10), -- allocate Information _ tax area
Per_information1 = trim (per_information1); -- distribute information_social Security phf Area
Commit;

 

This problem occurs when hr_employee_api.create_employee is used.

Select * from V $ parameter t where T. Name = 'nls _ date_format'

View the date format of the client.