Contributor Relations Grads To Alumni AE AV_CONF_ALUM Produces Unique Constraint Error (Doc ID 1326704.1)

Last updated on MARCH 06, 2017

Applies to:

PeopleSoft Enterprise CS Contributor Relations - Version 9 to 9 [Release 9]
Information in this document applies to any platform.

Symptoms

On : CS9 version, Constituent Info

When attempting to run the Graduates to Alumni app engine process AV_CONF_ALUM
the following error occurs.

ERROR
-----------------------
File: e:\pt84923b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1603 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_AV_CONFR_ALUM) violated
Failed SQL stmt:INSERT INTO PS_AV_CONFR_ALUM (OPRID , RUN_CNTL_ID , INSTITUTION , EMPLID , COMPLETION_TERM , DEGR_CONFER_DT , NAME , DESCR , CNST_TYPE , ACAD_CAREER , REPORT_ONLY) SELECT DISTINCT 'OTERO' , 'OTERO' , A.INSTITUTION , A.EMPLID , A.COMPLETION_TERM , A.DEGR_CONFER_DT , B.NAME , C.DESCR , 0 , 'GRAD' , 'N' FROM PS_ACAD_DEGR A , PS_PERSONAL_DATA B , PS_INSTITUTION_TBL C WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = C.INSTITUTION AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_INSTITUTION_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND A.INSTITUTION = 'PSUNV' AND A.ACAD_CAREER = 'GRAD' AND A.COMPLETION_TERM = '1983' AND A.DEGR_CONFER_DT IS NOT NULL AND A.ACAD_DEGR_STATUS = 'A'

Process 509167 ABENDED at Step AV_CONF_ALUM.MAIN.Step05 (SQL) -- RC = 805 (108,524)

STEPS
-----------------------
The issue can be reproduced on the customers db with the following steps:
1.Navigate to Contributor Relations > Constituent Information > People > Process Biodemo Information > Graduates to Alumni
2. Add A New Value and Create a Run Control as follows:
Make sure the Report Only option is UNCHECKED.
If not auto-populated, enter Academic Institution.
Use dropdown to select Academic Career and completion term.
3. Click Run and then hit refresh till the No success shows

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot run the grads to alumni process.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms