Error 'ORA-20001: Primary address already exists in date range' In HR_PERSON_ADDRESS_API.UPDATE_PERSON_ADDRESS API When Updating Date (Doc ID 762093.1)

Last updated on MAY 30, 2019

Applies to:

Symptoms

Problem Statement:

When attempting to update an address using the api HR_PERSON_ADDRESS_API.UPDATE_PERSON_ADDRESS where are two primary addresses exists, the following error occurs:

Steps to Reproduce:
The issue can be reproduced at will with the following steps:

1.  Create a new address using hr_person_address_api.create_person_address
   
   
2. Create another address primary address:
   a) End date the current address using hr_person_address_api.update_person_address
   b) Insert a new address using hr_person_address_api.create_person_address both of the above should be done in one transaction.
    
3.  Change the start date of the new address:
   a) Update the DATE_TO of the old address using hr_person_address_api.update_person_address
   b) Update the DATE_FROM of the new address using hr_person_address_api.update_person_address

The API will fail when step 3.a is performed. It works fine when it is done in PUI Forms Interface.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.