My Oracle Support Banner

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:

Oracle Human Resources - Version 12.0.4 and later
Information in this document applies to any platform.
This problem can occur on any platform.


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:

ORA-20001: Primary address already exists in date range.


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.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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