SQL Error encountered while testing fix for Bug 27521429 - UCAS - CRIMINAL RECORD DECLARED FOR CLEARING CHOICES

(Doc ID 2415010.1)

Last updated on JUNE 25, 2018

Applies to:

PeopleSoft Enterprise CS Recruiting and Admissions - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Recruiting and Admissions Other

When testing the fix for the Bug 27521429 to resolve the issue with criminal conviction records encountered the following error.

Loading Data into staging table from view ivStarZ1. (14250,57)
SQL error. Function: SQLExec
 Error Position: 0
 Return: 1400 - ORA-01400: cannot insert NULL into
("SYSADM"."PS_SAD_UC_I_STARZ1"."SAD_UC_CRMREC_DECL")
 Statement: INSERT INTO PS_SAD_UC_I_STARZ1 (INSTITUTION ,SAD_UC_APPCODE
,SAD_UC_PERS_ID , SAD_UC_CHOICENO ,SAD_UC_TIMESTAMP ,SAD_UC_CEFNO
,SAD_UC_INST ,SAD_UC_COURSE ,SAD_UC_CAMPUS ,SAD_UC_FACULTY
,SAD_UC_ENTRYYEAR ,SAD_UC_ENTRYMONTH ,SAD_UC_ENTRY_POINT ,SAD_UC_RSLT
,SAD_UC_REPLY , SAD_UC_IMP_STATUS) ( SELECT :1 ,NVL(APPLICATIONSCHEMECODE ,
APPNO) ,PERSONALID , 9 ,TIMESTAMP ,NVL(CEFNO, 0) ,NVL(INST, ' ') ,NVL(COURSE,' ')
,NVL(CAMPUS,'*') ,NVL(FACULTY,' ') ,NVL(ENTRYYEAR,0) ,NVL(ENTRYMONTH,0)
,NVL(ENTRYPOINT,'0') ,NVL(RESULT,' ') ,NVL(REPLY,' ') ,'N' FROM IVSTARZ1 @ UCAS A
WHERE ( A.TIMESTAMP > TO_TIMESTAMP(:2,'YYYY-MM-DD-HH24.MI.SS.FF') OR
TO_TIMESTAMP(:3,'YYYY-MM-DD-HH24.MI.SS.FF') IS NULL) AND INST= :4 AND ( ( :5 =0 AND
NVL(ENTRYYEAR,0) >= 2009 ) OR ( :6 <> 0 AND NVL(ENTRYYEAR,0) >= :7) ) AND (NULL IS
NULL OR PERSONALID = NULL) AND (NULL IS NULL OR APPLICATIONSCHEMECODE = NULL)
)
 Original Statement: %Sql(SAD_UC_IVSTARZ1,UCAS)
SQL error in Exec. (2,280) SAD_UC_STG.UCGTStage.OnExecute Name:process PCPC:3751
Statement:55
Called from:SAD_UC_STGAP.CHOICES.GBL.default.1900-01-01.Step11.OnExecute Statement:6
Process 3987037 ABENDED at Step SAD_UC_STGAP.CHOICES.Step11 (PeopleCode) -- RC = 24
(108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

- this list of columns doesn’t include SAD_UC_CRMREC_DECL and as it is NOT NULL this is giving this error.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Download and apply fix for Bug 27521429


Changes

 

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