[DAC] Problems With Index Creation During Full Load

Goal

We are executing Full Loads of Financial Analytics module. The ETL process always ends with fails. When we check the log file in DAC home directory (DAC_HOME\bifoundation\dac\log) we found the following error messages:

ANOMALY INFO::: DataWarehouse:CREATE UNIQUE INDEX 
W_EMPLOYEE_DS_U1 
ON 
W_EMPLOYEE_DS
(
INTEGRATION_ID ASC
,DATASOURCE_NUM_ID ASC
,SRC_EFF_FROM_DT ASC
) 
NOLOGGING
MESSAGE:::ORA-01452: no se puede CREATE UNIQUE INDEX; se han encontrado claves duplicadas

EXCEPTION CLASS::: java.sql.SQLException

Solution

***********
Query 1
***********
select person_id, effective_start_date, effective_end_date
from per_all_people_f
where person_id in
(
select person_id
from per_all_people_f
group by person_id, effective_start_date
having count(*) > 1
)
order by 1,2,3

Query 2
***********
select user_id, start_date, end_date
from fnd_user
where user_id in
(
select user_id
from fnd_user
group by user_id,start_date
having count(*) > 1
)
order by 1,2,3

Query 3
************
select assignment_id, effective_start_date, effective_end_date
from per_all_assignments_f
where assignment_id in
(
select assignment_id
from per_all_assignments_f
group by assignment_id, effective_start_date
having count(*) > 1
)
order by 1,2,3

************************************
A single employee (person_id for Query 1) has more than one record with the
same effective date, but with different effective to date.
This is technically possible because the database uniqueness depends on
person_id, effective_start_date, effective_end_date, all three of them.
However, this is functionally not possible. We have tried it through the
application GUI, and there's no way one could create such record-sets from
the front end. Having two records effective at any given point is
meaningless. This is the reason we have called it off as a bad data. The
reason for this is not clear, but looks like if the data is upgraded from
1158 instance or earlier, the upgrade program causes this.

Ideally, the Effective Start Date for the second record should be one day
after the Effective End Date of the first record.

This is what we have to update.

How to fix ?
************
The fix is being mentioned for PER_ALL_PEOPLE_F (query 1 above). If the issue
exists for FND_USER (query 2 above), similar steps will fix there as well.

STEP 1
======
There are a bunch of triggers defined against the table PER_ALL_PEOPLE_F.
Disable all the triggers for this table.

STEP 2
======
Depending on the result of Query 1, the update SQL query may be complicated.
It is better we do this one at a time. The idea is explained below.
Let's say the query 1 returns the data this way :

person_id effective_start_date effective_end_date
6272 04-JAN-1991 06-FEB-2002
6272 04-JAN-1991 31-DEC-4712

Effective start date of a record should be one day greater than the previous
records' effective end data. In the above example, we would need to update
the second records' effective start date to (06-FEB-2002 + 1 day). The
following SQL will do that :

Update per_all_people_f
set effective_start_date = to_date('02/07/2002', 'mm/dd/yyyy')
where
person_id = 6272 and
effective_end_date = to_date('12/31/4712', 'mm/dd/yyyy')

This needs to done for each distinct person_id that query 1 returns. If query
1 returns more than 2 records for any single person_id, then the update
should work accordingly. Example is as follows :

Bad set
=======
person_id effective_start_date effective_end_date
6272 04-JAN-1991 06-FEB-2002
6272 04-JAN-1991 06-JUL-2007
6272 04-JAN-1991 31-DEC-4712

Good set
=======
person_id effective_start_date effective_end_date
6272 04-JAN-1991 06-FEB-2002
6272 07-FEB-2002 06-JUL-2007
6272 07-JUL-2007 31-DEC-4712

STEP 3
======
Commit the change.

STEP 4
======
Enable back all the triggers on this table.