Thursday, October 20, 2011

effective_start_date and effective_end_date are invalid in per_all_people_f

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
Problem:    effective_start_date and effective_end_date are invalid in per_all_people_f
On : 11.5.10.2 version, Person / Assignment Issues
Find there are 12 people on the system which have data corruption. Their person record, has rows which show the Effective Start Date is greater than the Effective End Date

HR_6881_HRPROC_ORA_ERR
SQLERRMC ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.PY_ROLLBACK_PKG", line 3230
ORA-06512: at line 2

SQL_NO 4809
TABLE_NAME py_rollback_pkg.rollback_payroll_action
APP-PAY-06881: Error ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.PY_ROLLBACK_PKG", line 3230
ORA-06512: at line 2
has occurred in table py_rollback_pkg.rollback_payroll_action at location 4809

Cause: an oracle error has occurred. The failure was reported on table py_rollback_pkg.rollback_payroll_action at location 4809 with the error text ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.PY_ROLLBACK_PKG", line 3230
ORA-06512: at line 2
Symptoms:
The issue can be reproduced at will with the following steps:
1. Responsibility : UK HRMS Manager
2. Select : People > Enter & Maintain
3. Query for the person
Log files:


Solution:


The issue was cause by Data Corruption
Bug 9475418 - DATA CORRUPTION FOR 12 PEOPLE IN PER_ALL_PEOPLE_F 

Please can you run the below script on your test environment and re-test the issue :

----------------------------------------------------------
DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 10359
AND EFFECTIVE_START_DATE = TO_DATE('01-JUN-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 13592
AND EFFECTIVE_END_DATE = TO_DATE('31-JAN-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID =1425
AND EFFECTIVE_START_DATE = TO_DATE('17-NOV-2009','DD-MON-YYYY')
AND EFFECTIVE_END_DATE <> TO_DATE('31-DEC-4712','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 2001
AND EFFECTIVE_END_DATE = TO_DATE('26-May-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 8730
AND EFFECTIVE_END_DATE = TO_DATE('28-OCT-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 9182
AND EFFECTIVE_START_DATE = TO_DATE('30-JUN-2008','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 1280
AND EFFECTIVE_END_DATE = TO_DATE('23-MAR-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 1296
AND EFFECTIVE_END_DATE = TO_DATE('28-OCT-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 1353
AND EFFECTIVE_END_DATE = TO_DATE('28-OCT-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 320
AND EFFECTIVE_END_DATE = TO_DATE('28-OCT-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID =672
AND EFFECTIVE_END_DATE = TO_DATE('09-OCT-2009','DD-MON-YYYY');

DELETE FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = 943
AND EFFECTIVE_END_DATE = TO_DATE('25-MAY-2009','DD-MON-YYYY');

COMMIT;
References:


Bug 9475418 - DATA CORRUPTION FOR 12 PEOPLE IN PER_ALL_PEOPLE_F 

Get Oracle Certifications for all Exams
Free Online Exams.com

No comments: